Google-sheets – Summarize the best options in another table

google sheetsgoogle-sheets-queryvlookup

I need help to create a sheet for a game. I am providing the INPUT, and I would like to create the OUTPUT in another tab (do not consider the colors, its just for ease of understanding):

Mainly, the INPUT will be many locations with different items and each item with a % chance of being dropped in that location.

The OUTPUT need to be a summary of each item (only once and ordered by Item name, if possible) showing the location with the higher drop chance.

Is this possible?
If no, how could I change my INPUT to achieve an OUTPUT like this?

Best Answer

The following works, but requires you to have a hidden column. First, a query, let's say in cell D3:

=query(A3:C, "select max(C), B group by B order by B desc label max(C) ''", 0)

This selects the maximal % for each name, and orders names alphabetically listing only once. The "label" part avoids an unnecessary column label. The output will have two columns: D and E. Column E is what it should be in your output. Column D is the "chance" that you want to have in G, but since it's in a wrong place, it may be hidden.

Then in cell F3 use

=arrayformula(vlookup(filter(D3:D, len(D3:D)), {C3:C, A3:A}, {2, 1}, False))

which will look up every nonempty value in column D (that is, a max %) in column C of the sheet, and return the corresponding value from A, as well as from C itself. That is, it gives the location and chance.

Related Solutions

Google-sheets – In a Google spreadsheet, how to list unique items, modified by a regexp

My approach would be to "normalise" the data first:

=ArrayFormula(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B))

and then you can actually wrap this directly in a QUERY function to produce the desired output:

=ArrayFormula(QUERY(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B);"select Col1, sum(Col2) where Col1 != '' group by Col1 label sum(Col2) ''";0))

You can even output a header row as well:

=ArrayFormula(QUERY(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B);"select Col1, sum(Col2) where Col1 != '' group by Col1 label Col1 'Food Item', sum(Col2) 'Total'";0))

Google-sheets – How do subtract value in two consecutive rows and print it in a column

It's not entirely clear what you're after, but I think I have the idea.

You have this data:

   |   A    |   B
----------------------------------------------------
 1 | 12760 
 2 | 15464
 3 | 17162
 4 | 19808
 5 | 22757
 6 | 25450

And you want column B to show the difference between the value in column A and the value in column A in the row above. That's really quite simple.

In B2 put the formula:

=A2-A1

Then you can autofill that down the rest of column B as far as you need.

For the first row, since there's an implied zero, just put in B1

=A1

In the end, you'll have:

   |   A    |   B
----------------------------------------------------
 1 | 12760  |  12760
 2 | 15464  |  2704
 3 | 17162  |  1698
 4 | 19808  |  2646
 5 | 22757  |  2949
 6 | 25450  |  2693

(If this isn't what you're after, then please edit your question to provide further clarity.)

Best Answer

Related Solutions

Google-sheets – In a Google spreadsheet, how to list unique items, modified by a regexp

Google-sheets – How do subtract value in two consecutive rows and print it in a column

Related Topic