I have a Google Sheet with daily ranked items expanding with new data everyday in the Raw Data Table A2:C.

I would like to generate a report matrix similar to table in E2:J using one formula so that we don't need to manually copy formulas in each cells in the report table from time to time.

It would be ideal to have one formula at cell E2 (or E3, F1, F2) only that will automatically generate as many rows and columns in the report table based on the data in Raw Data Table. This way, the report table will not needed to be maintained on a daily basis (like copying formula everyday as data expanded).

There are less elegant ways to do it like pre-copy formula into a large number of cells or using Google App Script to generate the table. However, I believe an advance query() or a complex formula mixing query(), filters() and/or arrayformula() can do the job! Just wonder if anyone of the Google Sheets query() expert can help!

CodePudding user response：

try:

=QUERY(A2:C; "select A,max(C) where A is not null group by A pivot B"; 1)

CodePudding user response：

Here's a possible solution:

=ArrayFormula(LAMBDA(dates,ranks,{A2,ranks;dates,IFNA(VLOOKUP(dates&ranks,{A3:A&B3:B,C3:C},2,0))})
(UNIQUE(A3:A),TRANSPOSE(UNIQUE(SORT(B3:B)))))