Forum Discussion
Data Filtering by Column/ Horizontal
Even without LET() your code will be very similar, only with global names.
My variant is
=UNIQUE(
INDEX(Range,
MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2,
INT( SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1),,(ROWS(Range)-1)*2)/
(ROWS(Range)-1))
)
)More interesting is spill for counts, here LET could be more useful. Without it
=TRANSPOSE(
MMULT(
SEQUENCE(1,(COLUMNS(Range)-1)*(ROWS(Range)-1),,0),
(
INDEX(Range,
MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2,
INT( SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1),,(ROWS(Range)-1)*2)/
(ROWS(Range)-1))
)=TRANSPOSE(K6#))*
(INDEX(Range, MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2, 1 )>=$L$3)*
(INDEX(Range, MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2, 1 )<=$N$3)
)
)Not sure how to combine both spills together.
First an observation. Anything that can be done with LET could be done with named formula, I have used such calculations for years. What you lose is the 'calculate once, use many times'; that would require helper ranges and not defined names.
The following gives both columns from a single spill but I wouldn't care to make any claims concerning computational efficiency!
= LET(
\1, "Filter range by date",
firstRow, XLOOKUP(Start, NameTable[Date], NameTable, ,1),
finalRow, XLOOKUP(End, NameTable[Date], NameTable, ,-1),
filteredNames, firstRow:finalRow,
\2, "Calculate frequencies for filtered table",
freqTable, COUNTIFS(filteredNames, filteredNames),
\3, "Unpivot both tables",
m, ROWS(filteredNames),
n, COLUMNS(filteredNames)-1,
k, SEQUENCE(m*n),
rowNum, 1+QUOTIENT(k-1,n),
colNum, 1+MOD(k-1,n),
nameList, INDEX(filteredNames, rowNum, 1+colNum),
freqList, INDEX(freqTable, rowNum, 1+colNum),
\4, "Combine name and frequency lists",
combined, IF({1,0}, nameList, freqList),
\5, "Sort unique on combined name and frequencies",
SORT(UNIQUE(combined)) )My indentation is not as interesting as yours because, in the main, I have used local names in place of nested formulas. The jury is probably still out on whether one should write formulas like this; the fact that one can still has some shock value at the moment!
I have nothing against LET() in general except it is now only on beta channel. Thus it could be used mainly for modelling purposes, but not on practice for real projects.
My use of LET is pure self-indulgence; though I am particularly interested in developing techniques that exploit the new function. Before installing Office 365, I used to use named formula to bypass the dreaded implicit intersection. That process still works, but it is now more convenient to develop formulas using LET and, also, the parameters are then presented in a logical sequence, so providing self-documenting formulas. Paste Names was all very well but the logic didn't exactly shine through.
For client work, I would clearly not be able to use LET. A contract that required direct referencing and non-array solutions, it would simply turn down; if that is what is want, they can do it themselves 🙂