Forum Discussion
Find last MATCH for each row on a 2D running array with ONE "spill" formula
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)))) )
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.
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.
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 FinalTableI was doing fine until I got to the bit where the magic happens:
FinalTable = Table.FromColumns( #"Sorted Rows"[Count], #"Sorted Rows"[#"Stock ticker"] )where I was left wondering "where did that come from?" and "what is the logic that led to the function's existence?"
There is such a huge overlap in functionality across the various parts of Excel that it is frequently unclear which route to follow. I remember days of "the answer is the pivot table; now tell me the question". Now I assume the pivot table adherents have moved en-masse to Power Query / Power Pivot, while macro enthusiasts might be considering their VBA / Typescript options. Meanwhile primitive spread-sheeting has changed out of all recognition to become a respectable coding option with dynamic arrays and Lambda functions. Despite that, most will probably stick to the old manual work practices, no doubt supported by increasingly sophisticated AI.
For me, the main influence is probably my personal prejudices but, beyond that, I tend to think of PQ for external data sources and large datasets (~100,000 rows). In situations in which the spreadsheet is generating data as opposed to consuming it, I tend to think of array formulae but probably limited to small/medium sized problems (~10,000 rows and fewer columns). VBA, I tend to limit to event handlers, generating sketches to illustrate engineering data and uploading formulae to name manager: odd-ball activities that are not addressed elsewhere. Despite that, I have come across developers for whom the worksheet is merely a blank canvas and the processing is all macro driven.
Something I would love to see is an authoritative guide as to best practice for generating different classes of solution for differing end-users. Personal prejudice can only take one so far when it is so difficult to gain an oversight of the options.
