Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #542 May 16, 2012 www.energyefficientbuild.com.
user digitaltoast said:
Filter first of unique values where one column has the duplicated values
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.
I tried this: http://spreadsheets.
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
No comments:
Post a Comment