Forum Discussion
Solved
Find last MATCH for each row on a 2D running array with ONE "spill" formula
Below is a short snippet of a particular column with a list of stock tickers, which is part of a huge multi-column table: In order to get to a 2D array of that column, accumulating the rows...
leolapa I'm not quite sure if you're looking to replicate the full green area, or just the last row, but if just the last row, how about this?
=LET( ticker_list,$A$2:$A$21, uniq,TRANSPOSE(SORT(UNIQUE(ticker_list))), rows_,SEQUENCE(ROWS(ticker_list)), MAP(uniq,LAMBDA(u,MAX(FILTER(rows_,ticker_list=u)))) )
PeterBartholomew1I think the "e-1" and "e>1" are leaving out the last entry in StockTicker.
True. By going up to, but not including, the current row, the final row is never returned and the first row of the result table is blank. I was simply trying to replicate a set of numbers with little or no insight as to the purpose of the calculation!
Added:
Just taken a look at the screen shot in the OP. Seems the adjustment is not needed. Thanks.