Google-sheets – Selecting row with best value for items with several versions/upgrades

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

I'm sure my problem is quite simple to solve, but I've been on it for a while and I could use some help.

I have a huge table, but the problem boils down to this: some items have 2 versions (one normal and one upgraded), and I'd like to be able to show only the best version of an item (upgraded if it exists, the normal one otherwise).

Example table:

Name       | Value | Type
Tool A     | 50    | Tool A
Tool A [U] | 75    | Tool A
Tool B     | 100   | Tool B
Tool C     | 200   | Tool C
Tool B [U] | 150   | Tool B

And I'd like to get

Name       | Value | Type
Tool A [U] | 75    | Tool A
Tool C     | 200   | Tool C
Tool B [U] | 150   | Tool B

Note: there can only be one upgrade, and the base version of the "tool" always exists if that helps.

I can easily select the best value for tool A, B or C, but selecting the name (and other properties) with it is the problem.

I tried something like SELECT A, MAX(B), C GROUP BY C, but it's throwing errors back at me.

Best Answer

={"Name", "Value", "Type"; 
 ARRAYFORMULA(QUERY(IFERROR(VLOOKUP(UNIQUE(C2:C), QUERY({ROW(A2:A), A2:C}, 
 "select Col4,Col1,Col2,Col3 
  where Col2 is not null 
  order by Col2 desc", 0), {1,2,3,4}, 0)), 
 "select Col3,Col4,Col1 
  where Col1 is not null 
  order by Col2"))}

Related Solutions

Google Sheets – How to Select Highest Rated Beers in a Pivot Table

The FILTER command does this nicely. Suppose this is your table.

       A         B       C
1    Beer    Brewery    Rating
2    abc        QQ      1
3    qwe        WW      3
4    wer        EE      5
5    ert        WW      2
6    dfg        QQ      4
7    sdf        WW      3

Then add

9   Brewery  MaxRating    Favorites
10   WW

where

B10 = MAX(FILTER(C2:C7, B2:B7=A10)

which find the best rating for the brewery listed in A10; and

C10 = JOIN(", ", FILTER(A2:A7, B2:B7=A10, C2:C7=B10))

which finds all beers from that brewery with the maximal rating. Result:

9   Brewery  MaxRating    Favorites
10   WW          3         qwe, sdf

Google Sheets – Return Array of Cells from a Table

One approach is to filter the table before querying it. Here is an example where the table is in A1:D7 and the choice of product is in cell F1:

=query({A1:A7, filter(B1:D7, B1:D1 = F1)}, "select * where Col2 > 0")

Here, filter keeps only the column where the column name matches F1. The array notation {} makes a two-column array with the first column coming from A1:A7 (ingredient names) and the second being quantities. Finally, query selects the rows where the quantity is positive.

And this is a more decorated version of the above, which displays "no recipe found" when there are no matches, and omits the column names in the output by using label ... ''.

=iferror(query({A1:A7, filter(B1:D7, B1:D1 = F1)}, "select * where Col2 > 0 label Col1 '', Col2 ''"), "no recipe found")

Best Answer

Related Solutions

Google Sheets – How to Select Highest Rated Beers in a Pivot Table

Google Sheets – Return Array of Cells from a Table

Related Topic