How do you use ArrayFormula with arrays (after aggregation)?

user_14027803 0
Viewed 59

In my example:

https://docs.google.com/spreadsheets/d/1QQNTw_r9-q-FqVNwUoYklup73niZCFyO0VDUYImP5fo/edit?usp=sharing

I'm using Google Forms as an eBay clone to sell rare items. Each bid is outputted from the form to the "Data" worksheet and then I have ArrayFormulas set up inside the "Processed" worksheet. The idea is that I want to process the bids so that we filter everything except the items with the highest bids. All data should be automatically updated, hence why I want to use ArrayFormulas.

My strategy is that in colum A, I first filter all unique items (=unique(filter(Data!A2:A,Data!A2:A<>""))) and end up with:

  • Jurassic Park 6-Pog Hologram Set
  • Princess the Bear TY Beanie Baby
  • Holographic 1st Ed Charizard

However, then in column B, we have to find the highest bid that corresponds to that unique item, e.g.:

=IF(ISBLANK(A2),,ArrayFormula(MAX(IF(Data!A2:A=A2,Data!B2:B))))

However, I don't want to have A2 be a single cell (A2) but an array (A2:A) so that it doesn't have to be manually copied down the rows. Similarly, I also want columns D and E to be automatic as well. Is there any way to achieve this?

google-sheets-formula transpose google-sheets sorting arrays
3 Answers

Not sure if it would be considered easier than the previously posted answer, but in case this thread is found in the future, I think that this is a slightly simpler way to solve these kinds of problems:

Try this on a fresh tab in cell A1:

=FILTER(Data!A:D,COUNTIFS(Data!A:A,Data!A:A,Data!B:B,">"&Data!B:B)=0)
user_110751290

I did some research and found an answer very similar to what you were looking for. After rearranging the formula slightly to match your sheet, I was able to get this to work: =ArrayFormula(vlookup(query({row(Data!A2:A),sort(Data!A2:C)},"select max(Col1) where Col2 <> '' group by Col2 label max(Col1)''",0),{row(Data!A2:A),sort(Data!A2:D)},{2,3,4,5},0))

Sheet

This formula automatically populates product name, highest bid, username, and timestamp. I ran some tests, adding my own random names and values into the data sheet, and the formula worked great.

Reference: https://webapps.stackexchange.com/a/75637

user_118612180

use:

={A1:D1; SORTN(SORT(A2:D, 1, , 2, ), 9^9, 2, 1, )}

enter image description here

translated:

{A1:D1} - headers
SORT(A2:D, 1, , 2, ) - sort 1st column then 2nd column descending
9^9 - output all possible rows
2 - use 2nd mode of sortn which will group selected column
1 - selected column to be marged based on unique values
user_56326290
Related