Forum Discussion
Additional help needed with existing formula using LAMDA- Excel 365
I reorganized the sample file, so each table now contains the same number of columns, in the same order, as the updated SheetMap. The only thing I had to change about the formula (from rev3.0) was the _cId variable to set the field mapping:
=LET( _rep, RESULTS!$A$2, _beg, RESULTS!$B$2, _end, RESULTS!$C$2, _lst, RESULTS!$A$5:$A$12, _inc, (RESULTS!$E$5:$E$12="EXCEED")+(RESULTS!$E$5:$E$12="NOT MET"), IF( NOT(OR(_inc)), "[no records found]", LET( VALλ, LAMBDA(lbl,tId,csv,LAMBDA(x,CHOOSE(x,lbl,tId,csv))), EXPλ, LAMBDA(array,[rws],EXPAND(array,rws,9,"")), INCλ, LAMBDA(field3,field2,(field3=_rep)*(field2>=_beg)*(field2<=_end)), _fId, SEQUENCE(,9), _cId, VSTACK("1,2,3,4,,,5,13,14", "1,2,3,4,,6,5,10,11", "1,2,3,4,,,5,11,12", "1,2,3,4,6,,,8,9", "1,2,3,4,,,5,11,12", "1,2,3,4,7,6,5,12,13", "1,2,3,4,6,,5,14,15", "1,2,3,4,6,7,8,14,19"), REDUCE( EXPλ("Field"&_fId,2), FILTER(MAP(_lst,SEQUENCE(ROWS(_lst)),_cId,VALλ),_inc), LAMBDA(acc,val, LET( tbl, INDIRECT("tbl"&SUBSTITUTE(SUBSTITUTE(val(1)," ",),"-",)&"[#ALL]"), arr, SORTBY(EXPλ(CHOOSECOLS(tbl,--TEXTSPLIT(val(3),",",,1))),SORTBY(_fId,TEXTSPLIT(val(3),",")="")), bdy, DROP(arr,1), qry, FILTER(bdy,INCλ(CHOOSECOLS(bdy,3),CHOOSECOLS(bdy,2))), VSTACK(acc,EXPλ("["&val(2)&"] "&val(1)&":"),EXPλ(TAKE(arr,1)),EXPλ(IF(ISBLANK(qry),"",qry),ROWS(qry)+1)) ) ) ) ) ) )I also added a 'FieldMap' worksheet to the attached revision, so you can see how the field mapping was generated (there are 4 formulas highlighted in blue, as well as a note showing the formula used as the Data Validation List Source for range C5:U12).
If you're still having issues applying these methods to your master workbook, I'm afraid there's not much more I can do to help aside from gaining access to your actual workbook to troubleshoot the problem.
Best of luck!
wow I sort of remember this. lol.
here is the updated function:
=LET(filterBy, RESULTS!A2:C2,
resultTable, RESULTS!A5:E12,
columnCounts, {5,6,5,5,5,7,6,7},
report, LAMBDA(result_filter,result_row,table_all, LET(table_data, DROP(table_all,1),
table_matches, (CHOOSECOLS(table_data,3)=INDEX(result_filter,1))*
(CHOOSECOLS(table_data,2)>=INDEX(result_filter,2))*
(CHOOSECOLS(table_data,2)<=INDEX(result_filter,3)),
IF((TAKE(result_row,,-1)="EXCEED")+(TAKE(result_row,,-1)="NOT MET"),
VSTACK("*** "&INDEX(result_row,1)&" ***",
TAKE(table_all,1),
FILTER(table_data, table_matches, "")),
""))),
total, REDUCE("",SEQUENCE(ROWS(resultTable)),LAMBDA(p,q,VSTACK(p,
LET(tbl, INDIRECT("tbl"&TEXTJOIN(,,TEXTSPLIT(INDEX(resultTable,q,1),{" ","-"}))&"[#ALL]"),
report(filterBy, CHOOSEROWS(resultTable,q), CHOOSECOLS(tbl,SEQUENCE(,INDEX(columnCounts,q)),SEQUENCE(,2,COLUMNS(tbl)-1))))
))),
IFNA(IF(total=0, "", total),""))on line 8 above it used to just check if column 2 was a number but now checks if that last column is specifically either "EXCEED" or "NOT MET"
hope that helps.
in the attached it is in the 'Results New' tab
That did the trick for displaying the correct categories. However, each category that does not appear is still producing a certain number of blank lines between and, oddly, after the records for the displayed categories, there are 3 lines with !Value# errors in them. Not sure what that's about, but is there a way to fix so that only one blank line appears between the categories to separate them?