How to de-duplicate rows in Google Sheet based on the first column only?

@daveagill @Freeboots @hieunc @adroitboss Thank you a lot for the time and trying to help. Here I recorded a short video going trough the solution. https://youtu.be/tomNAHbXr5E

Here is my attempt at winging this with formulas: https://docs.google.com/spreadsheets/d/1WBlnaIODteIQL52jjp65rE1aY1PxdqbezPzJ3sbMQgs/edit?usp=sharing

The cells with formulas are highlighted in yellow in the sheet.

Explanation:

The 'is duplicate?' column F simply tells us if the row is a duplicate or not with this formula:

=COUNTIF(A$1:INDIRECT("A" & Row()), INDIRECT("A" & Row())) > 1

The INDIRECT("A" & Row()) parts just give us a cell reference based on the current row number, if the row is row #5 then it constructs a reference to A5.

So as you read down column F we can basically interpret the formula as:

=COUNTIF(A$1:A2, A2) > 1
=COUNTIF(A$1:A3, A3) > 1
=COUNTIF(A$1:A4, A4) > 1
=COUNTIF(A$1:A5, A5) > 1
.... etc ...

And you really could just write those in like that or use the mouse to drag the formula down, but I chose to use INDIRECT because you said rows were added dynamically and with INDIRECT we get to use literally the exact same formula down all rows which makes automating the addition of rows easier.

The COUNTIF is counting how many times the current row's 'A' cell value occur in a range from A1 down to the current row (so not the whole of column A, only from top-to-current-cell).

We consider the row to be a duplicate if there are previous occurrences of the cell's value above it in the table. Otherwise if there is only 1 occurrence (the one in our current cell) then it is considered unique.

Finally, the formula over in cell IJ uses the FILTER formula which works a lot like UNIQUE except that it has the ability to check our 'is duplicate' column and filters the table to select only unique rows:

=FILTER(A:D, F:F = FALSE)

Hi Stefan, its been a while since I did anything in GAppScript, but this guide does almost exactly what you're asking.

Scroll down to the end of the explanation, you will see a 'Variation' heading, be sure to read that section as it explains how to adjust the script to remove rows with matching data in particular columns.

In your case I think you would want to replace:

if(row.join() == newData[j].join()){ duplicate = true; }

with:

if(row[0] == newData[j][0]){ duplicate = true; }

This script will remove duplicates from the currently active (open) sheet. If you wish to alter it to run on a particular sheet at particular times or via an api call, thats beyond the scope of a comment here but certainly possible with a little tweaking.

Hey Stefan, just to confirm, you want to filter unique value based on column A, then remove the entire row wherever there is a duplicate, right?

If so, you can do the following:

1. Select the entire sheet (Ctrl + A)
2. From the top toolbar, choose Data > Remove Duplicates
3. In the Column to analyze, select only column A (in your case)
4. Click Remove duplicates and done

@AndrewKamphey Andrew, I don't know better person who can help with this on IH, so I am shamefully mentioning you.

And here the other guys: @jesselnieman @alexisgrant @CMOnline @hieunc @royledoyle