Wednesday, May 16, 2012

yogi_Filter First Set Of Unique Entries Where First Two Columns Have Duplicated Entries

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #542    May 16, 2012

user digitaltoast said:
Filter first of unique values where one column has the duplicated values
Take the following data:
A  |  B  |  C  |  D
21  Fish  Chips Ham
21  Fish  Chips Eggs
32  Toast Jam   Tea
32  Toast Jam   Coffee
33  Bacon Beans Sausage
I'd like to end up with
A  |  B  |  C  |  D
21  Fish  Chips Ham
32  Toast Jam   Tea
33  Bacon Beans Sausage
I realise that the other slightly differing rows will be "lost"; that doesn't matter in this case.
Been bashing away at this for half a day, teased by the following sentence elsewhere in this group:
"I think my favorite is the arrayformula(vlookup(...)) combo thanks to Adam! "
Helpfully, he didn't mention what it was, and I've searched this group high and low and can't find the example. 
This is one of those cases where Excel and GS differ, so that doesn't work. 
I delved into QUERY language, only to find that you can't GROUP by something unless you sum or aggregate it, and after an hour or so, I gave up understanding how you can group by a number when you apparently have to SUM various foodstuffs and trying to understand WHY you'd need to sum in order to group.
So, I'm left with pasting vlookups in every row and column; the problem is with 20 columns and 700+ rows of data, ie, 14,000 vlookups, you can imagine how quickly that kills my spreadsheet!
It seems such a simple thing to want to do, but I've also tried FILTER, UNIQUE and so on. Because the data differs slightly in the otherwise-identical columns, it all fails.
Pointers would be greatly appreciated, thanks!
following is a a solution to the problem