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!
Try the following revision and see if it meets your needs:
=LET(
_rep, RESULTS!A2,
_beg, RESULTS!B2,
_end, RESULTS!C2,
_inc, (RESULTS!E5:E12="EXCEED")+(RESULTS!E5:E12="NOT MET"),
IF(
NOT(OR(_inc)),
"[no records found]",
LET(
keys, FILTER(HSTACK(RESULTS!A5:A12,VSTACK(5,6,5,5,5,7,6,7)),_inc),
cols, MAX(DROP(keys,,1))+2,
EXPλ, LAMBDA(array,[rws],EXPAND(array,rws,cols,"")),
INCλ, LAMBDA(field3,field2,(field3=_rep)*(field2>=_beg)*(field2<=_end)),
REDUCE(
EXPλ("REPORT:"),
SEQUENCE(ROWS(keys)),
LAMBDA(acc,rId,
LET(
lbl, INDEX(keys,rId,1),
tbl, INDIRECT("tbl"&SUBSTITUTE(SUBSTITUTE(lbl," ",),"-",)&"[#ALL]"),
arr, HSTACK(TAKE(tbl,,INDEX(keys,rId,2)),TAKE(tbl,,-2)),
bdy, DROP(arr,1),
qry, FILTER(bdy,INCλ(INDEX(bdy,,3),INDEX(bdy,,2))),
VSTACK(acc,EXPλ("["&rId&"] "&lbl&":"),EXPλ(TAKE(arr,1)),EXPλ(IF(ISBLANK(qry),"",qry),ROWS(qry)+1))
)
)
)
)
)
)See attached, if needed...
EDIT: added an optional [rws] argument to the EXPλ function so the qry results can be expanded by 1 row to separate each table in the final output.
It didn't work. Just returns #VALUE! in the cell. Sorry, but I'm not familiar with what you are using. I can't really point to anything in the formula. Thanks for the try. :)
The problem is in column E (GOAL) on the RESULTS worksheet in your 082625 stripped-down file. It's not visibly obvious because the results are hidden from view (via conditional formatting) when column D="N/A". If you remove the formatting, you'll see the formulas in column E are returning #VALUE! errors when column D="N/A". Your original 061625 sample file had a different formula in this column that worked properly. Either restore the original formula, or try the following in cell E5 (and copy down):
=IFS(D5="n/a","",D5<F5,"NOT MET",D5>G5,"EXCEED",1,"MET")Once this is corrected, you should be able to successfully implement ANY of the methods I've suggested thus far. If you need help tweaking anything else thereafter, just let me know.
That fixed the issue! Thank you very much for sticking with me on this. Much appreciated!! I'm wondering if it would be possible to not show the 'Field x' that appears above the columns. No big deal if it's not an easy fix. We can certainly deal with it as is. Thanks again!!
Did you download the attached file, and it shows #VALUE! immediately after opening it?
Or did you just copy/paste the formula from my post directly into your master workbook? And if so, did you make sure all of the sheet/range references are pointing to the correct cells in your master workbook?
The formula works fine for me, even if I copy/paste it into any one of your sample files from the original thread. Differences in Regional and Language settings can sometimes affect the syntax, so it's best if you download the workbook I shared and try copying the formula from there.
Kind regards.
I looked at it and can't see anything (that I can decipher) pointing to something wrong. Could it be that there are other worksheets in the workbook that are not being referenced? I'm not sure because it seems to work fine on the one you sent with dummy values. Only difference, is that I removed the unused tabs and personal ID info from the spreadsheet.
I did not check the references when I copied and pasted. I will see if I can figure out what is what and correct any bad references. Sorry about that. I will get back to you. Thanks!
