CODEWITHSUNDEEP
google-sheets
DeeKay789
DeeKay789

Reputation: 367

Filter rows based on uniqueness of a column

I'm sure I'm overthinking a problem again. All I'm trying to achieve is a result array that only contains rows where the data in first column is unique and sorted.

So using an example array (say sheet 'DB')

A     B     C     D
Me    Pink  Car   Top
Me    Blue  Bike  Middle
You   Pink  Car   Bottom
They  Pink  Bike  Bottom
You   Blue  Bike  Top
Them  Red   Car   Middle
Them  Blue  Car   Top
Me          Bike  Middle
You         Car 
Us                Top

Results in another sheet (say 'Results')

A     B     C     D
Me    Pink  Car   Top
Them  Red   Car   Middle
They  Pink  Bike  Bottom
Us                Top
You   Pink  Car   Bottom

Ideally a single formula will go in 'Results!A1'.

Ignoring the SORT element for the moment, this sledgehammer only works for small arrays

=filter(A1:D13,mmult((A1:D13=transpose(A1:D13))*(row(A1:D13)>=transpose(row(A1:D13))),row(A1:D13)^0)=1)

but my DB table is 25,000+ rows (10+ wide) so seems to break the GS 2m cell limit (>matrix 1414*1414) - large arrays produce a 'Column expected 21,414, actual column 1' type error - rather than a more useful 'You broke a limit!'

Note. The above solution works best so far because the originating data is not sorted.

I've tried QUERY() but again, breaks on the UNIQUE() element.

=Query('DB!A2:I, "select * where Col1 matches '"&UNIQUE(DB!A2:A)&"'",0))

UNIQUE(...) works fine in terms of stripping duplicates in the key column but how do I then express the rest of the row to each unique entry?

UNIQUE({A1:A;FILTER({A1:D...)}) results in 'No values found'

I was hoping a simple SORT(UNIQUE(...)) would be the ideal solution but alas it seems I'm looking in the wrong haystack.

I've also tried the following structures but either the length of time taken to process or my syntax has crippled any result.

The resulting array btw will just be a helper array for a more rational user sheet that will use IMPORTRANGE() on the result above.

Upvotes: 0

Views: 703

Answers (2)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

This formula accomplishes this: 

=ARRAYFORMULA(if(istext(SORT(UNIQUE(Sheet3!A:A))),arrayformula(VLOOKUP(SORT(UNIQUE(Sheet3!A:A)),Sheet3!A:D,{1,2,3,4},false)),))

enter image description here

Upvotes: 0

Jack Brown
Jack Brown

Reputation: 5892

This should do the trick for the above data:

=sort(iferror(ARRAYFORMULA(VLOOKUP(UNIQUE(A2:A),A2:D,{1,2,3,4},false)),""),1,true)

Gives the following result:

Me      Pink    Car     Top
Them    Red     Car     Middle
They    Pink    Bike    Bottom
Us                      Top
You     Pink    Car     Bottom

if you have more columns you need to be included in your vlookup you can add it like so:

VLOOKUP(UNIQUE(A2:A),A2:(till which Column),{1,2,3,4,5... (Add your columns here)},false

However, note Vlookup will return the first row corresponding to the match in Column A. That means the order of the data in the input array matters.

Hope that helps!

Upvotes: 1

Related Questions