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

     

tboulden's avatar
tboulden
Iron Contributor
Sep 04, 2021

leolapaIf you're looking for the full green area, try this.

=LET(
    ticker_list,$A$2:$A$21,
    uniq,TRANSPOSE(SORT(UNIQUE(ticker_list))),
    rows_,SEQUENCE(ROWS(ticker_list)),
    match_rows,--(ticker_list=uniq)*rows_,
    MAKEARRAY(
        ROWS(match_rows),
        COLUMNS(match_rows),
        LAMBDA(i,j,
            IF(
                i=1,INDEX(match_rows,i,j),
               MAX(
                    INDEX(match_rows,SEQUENCE(i-1),j),
                    INDEX(match_rows,i,j)
                )
            )
        )
    )
)
  • tboulden's avatar
    tboulden
    Iron Contributor
    Sep 04, 2021

    Simplified for extraneous calc:

    =LET(
    ticker_list,$A$2:$A$21,
    uniq,TRANSPOSE(SORT(UNIQUE(ticker_list))),
    rows_,SEQUENCE(ROWS(ticker_list)),
    match_rows,--(ticker_list=uniq)*rows_,
    MAKEARRAY(
        ROWS(match_rows),
        COLUMNS(match_rows),
        LAMBDA(i,j,
            IF(
                i=1,INDEX(match_rows,i,j),
                MAX(
                    INDEX(match_rows,SEQUENCE(i),j)
                )
            )
        )
    )
)

     

     

Resources