How can I filter a full table based on only unique values in a single column?

Question

How can I deduplicate a table (all rows and columns) based on the values in column A?

Example:

What I want after deduplicating:

Basically, I only want unique values in column A, but I want all the other data to persist in the other columns, including cells with null values.

Also, all columns are derived from the =IMPORTDATA() function in the first cell of column A.

Answer 1

I found a way to do it:

1. Select Column A and "Insert 1 left" to add a column. Your table data is moved over one to columns B - D.
2. In your newly blank Cell A2, enter formula
   =IF(COUNTIF($B$2:$B2,B2)=1, "Unique", "Duplicate")
3. Copy the formula from A1 and paste it down the A column range for as far as you have data. The "$" absolute references will ensure everything works smoothly.
4. Add a new sheet. Copy and paste your old sheet's B - D column headers to the new sheet's A - C.
5. In the your new sheet's cell A2, enter formula
   =FILTER(OldSheet!B2:D5, OldSheet!A2:A5 = "Unique")
6. Your new sheet now has your old sheets data -- Deduplicated!

I use it with ImportRange to separate Data Entry from Data Processing, so ImportData shouldn't present a problem.

You can hide the A column on your old sheet if you like.

Best of all, everything updates live on edit. So you can continue adding data to OldSheet and NewSheet will be always be current with filtered rows.

Answer 2

I think:

deduplicate a table (all rows and columns) based on the values in Column A

is not compatible with

I only want unique values in column A, but I want all the other data to persist in the other columns, including cells with null values

so choosing the latter:

=UNIQUE(x:x)  

might serve where x represents whatever the column reference is for "ColumnA".

If the results are required in ColumnA, select the column with the formula, Copy, Paste special, Paste values only over the top then copy the result over the top of ColumnA.

If importdata brought in more than one column some additional rearrangement may be necessary.

Answer 3

This can be done with VLOOKUP -

=ARRAYFORMULA(VLOOKUP(UNIQUE(A2:A),{A2:A,A2:C},{2,3,4},False))

Answer 4

Move the column you want to make unique on the target sheet to the leftmost postions (make it column A). Create another sheet. On this new sheet make =UNIQUE() for the column on the target sheet you want to be unique. Then, on this new sheet, make =VLOOKUP() in next columns, with unique column as criteria and the target sheet working range as range, for each column of the target working range. This will be equivalent to deleting all rows with the value in the firs column which have already been present somewhere up the range (VLOOKUP works lazy, until the first match).

Answer 5

The cleanest way I know is with QUERY(). Example:

=QUERY(sheetX!A1:M100,"select min(A), min(B), min(F) group by F",1)

This returns one row for each unique value of column F.

-> Every field (column) you want to see must be in an aggregation function like MIN,MAX…

-> Put the field you want to have unique values in the "group by" clause.

Answer 6

I was unable to use =UNIQUE successfully. Here's what worked for me:

1. Paste values of sheet into Excel
2. Use the Advanced Filter tool
3. For the List range, select the column containing dupes
4. For the Criteria range select the whole sheet to be filtered
5. Select Unique records only

This resulted in a filtered range of data, based on the deduplication of Column A. Still not sure how I'd achieve this in Google Sheets.