Forum Discussion
Data Filtering by Column/ Horizontal
PeterBartholomew1 Thanks for your response. You solution for counting the number of occurrence is fine. But how would I get the unique records of names, if there are hundreds of names in the list.
The UNIQUE function requires the table to be unpivoted and that is a somewhat messy process.
= LET(
m, ROWS(NameTable),
n, COLUMNS(NameTable)-1,
k, SEQUENCE(m*n),
rowNum, 1+QUOTIENT(k-1,n),
colNum, 1+MOD(k-1,n),
list, INDEX(NameTable, rowNum, 1+colNum),
SORT(UNIQUE(list)) )Without the LET function, the process is worse. One would either have to implement each line as a separate named formula using Name Manager or, worse still, write it as a nested formula.
There are (paid) function libraries that can help. Charles Williams's FastExcel includes an UNPIVOT function which reduces the process of determining a sorted list of distinct names to
= LET(
unpivoted, UNPIVOT(NameTable,,1,,,0),
list, INDEX(unpivoted, ,3),
SORT(UNIQUE(list)) )
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.
