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

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 04, 2021

leolapa 

I think I have managed to reproduce your green table using MAP over 2D

 

= LET(
   k, SEQUENCE(ROWS(StockTicker)),
   target, Broadcastλ(ticker#, k),
   end, Broadcastλ(k,ticker#),
   MAP(target,end, LastMatchλ))

 

where LastMatchλ searches part of the StockTicker column for a specific ticker

 

"LastMatchλ"
= LAMBDA(t,e,
    LET(
      rng, INDEX(StockTicker,1):INDEX(StockTicker,e-1),
      IF(e>1, IFERROR(XMATCH(t,rng,,-1),0),0)
    )
  )

 

and Broadcastλ merely broadcasts a vector to form an array

 

"Broadcastλ"
= LAMBDA(x,y,
    IF(LEN(y),x)
  )

 

In doing so, I have removed the yellow helper range.

  • tboulden's avatar
    tboulden
    Iron Contributor
    Sep 04, 2021

    PeterBartholomew1I think the "e-1" and "e>1" are leaving out the last entry in StockTicker.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Sep 04, 2021

      tboulden 

      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.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 04, 2021

        PeterBartholomew1 , leolapa ,

        In preamble of the initial post

        --

        Below is a short snippet of a particular column with a list of stock tickers, which is part of a huge multi-column table

        --

        If the table is really huge (e.g. thousands of rows ) it is interesting to know how suggested solutions work on real data. Perhaps compare with Power Query solution, it's relatively simple (below, I took as the basis latest file from Peter).

         

        Thank you

        let
    Source = range,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(
        #"Added Index",
        {"Stock ticker"},
        {{"Count",
            each [
    //--------
    AppendIndex = Table.Combine({_, Index}),
    #"Sorted Rows" = Table.Sort(
        AppendIndex,
        {{"Index", Order.Ascending}, {"Stock ticker", Order.Ascending}}
    ),
    #"Added Custom" = Table.AddColumn(
        #"Sorted Rows",
        "ID",
        each if [Stock ticker] = null then null else [Index]
    ),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"ID"}),
    #"Filtered Rows" = Table.SelectRows(
        #"Filled Down",
        each [Stock ticker] = null
    ),
    #"Replaced Value" = Table.ReplaceValue(
        #"Filtered Rows",
        null,0,
        Replacer.ReplaceValue,{"ID"}
    ),
    Custom1 = #"Replaced Value"[ID]
        ][Custom1]
    //--------
        }}
    ),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Stock ticker", Order.Ascending}}),
    FinalTable = Table.FromColumns(
        #"Sorted Rows"[Count],
        #"Sorted Rows"[#"Stock ticker"] 
    )
in
    FinalTable

         

Resources