Forum Discussion

leolapa's avatar
leolapa
Brass Contributor
Sep 03, 2021
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...
excel
Formulas and Functions
office 365
  • tboulden's avatar
    tboulden
    Sep 04, 2021

    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))))
)

     

leolapa's avatar
leolapa
Brass Contributor
Sep 04, 2021

Thanks tboulden and PeterBartholomew1 for the great solution alternatives.

 

I also came up with my own, albeit much longer and therefore not near as elegant as the ones you guys came up with:

 

=INDEX(BYROW(--(INDEX(IF(--(SEQUENCE(;ROWS($A$2:$A$21);0)<SEQUENCE(ROWS($A$2:$A$21)))=1;TRANSPOSE($A$2:$A$21);"");SEQUENCE(ROWS($A$2:$A$21)*ROWS(UNIQUE($A$2:$A$21));ROWS($A$2:$A$21);;0)*SEQUENCE(ROWS($A$2:$A$21)*ROWS(UNIQUE($A$2:$A$21)))-(TRUNC(SEQUENCE(ROWS($A$2:$A$21)*ROWS(UNIQUE($A$2:$A$21));;0)/ROWS($A$2:$A$21))*ROWS($A$2:$A$21));SEQUENCE(ROWS($A$2:$A$21)*ROWS(UNIQUE($A$2:$A$21));ROWS($A$2:$A$21);;0)*SEQUENCE(;ROWS($A$2:$A$21)))=INDEX(SORT(UNIQUE($A$2:$A$21));ROUNDUP(SEQUENCE(ROWS(UNIQUE($A$2:$A$21))*ROWS($A$2:$A$21))/ROWS($A$2:$A$21);0)))*(SEQUENCE(ROWS($A$2:$A$21)*ROWS(UNIQUE($A$2:$A$21));ROWS($A$2:$A$21);;0)*SEQUENCE(;ROWS($A$2:$A$21)));LAMBDA(Last_Match_By_Row;MAX(Last_Match_By_Row)));TRANSPOSE(SEQUENCE(ROWS(UNIQUE($A$2:$A$21));ROWS($A$2:$A$21))))

 

 I'll definitely go with one of you guys' since either approach will make for a much leaner calculation mechanism, and avoid taking my workbook to a potential slow performance issue down the road...

 

SergeiBaklan for now I think a formulaic approach ought get the job I need done, but I sure appreciate your valuable input and the aforementioned code will sure help me on a future occasion.

 

P.S.:

I marked tboulden's as the best answer as I don't know another way to close this query as answered, but for sure any of the 3 proposed approaches are great.

Resources