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)))) )
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
FinalTable
I 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.
I'd do not differentiate only based on size of the data, even if that's important. Each approach has it's own limitations, there is no silver bullet.
If we far from physical limits, what to use (PQ, DA, VBA, js, etc) is mainly depends on our own skills and maintainability. If my clients don't use PQ I won't push them to use it. If I use Excel.script quite eventually I won't do production solution on it since not sure if will work with it in a year and doesn't know other people with such skills around. That could be other reasons to use this or that. On the other hand it could be and, not or. Chris Webb recently demonstrated how to use data model, cube formulas and dynamic arrays in one solution.