1

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.

0

={"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"))}

0

  • Thanks, it works for this example. However, could you quickly explain the idea of it? That would help me apply it to the actual table with 50+ columns and hundreds of entries. I get that you create 2 temporary tables and then compare them to get the wanted values, but that still appears like a black box to me (especially the use of Col4). Thanks! – Fnor Apr 12 at 23:44
  • sure. the idea is to assign a row number to every row (for purpose of sorting as the last step), then sort by column A desc which will put [U] before non-[U] value so we could use vlookup that will get only first match ([U]) if there are more than one values eg the [U] – user0 Apr 13 at 0:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.