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!
I think I found the problem. I forgot that I had moved one of the columns on the RRV/DVOP table to make the date columns line up better on the REPORT tab. I have now adjusted the SheetMap accordingly. Here is the corrected SheetMap. I'm really sorry about that. Would you mind a final adjustment to the formula that takes this into account? Please let me know if you would need anything else. Thank you!
Hi Marcus,
If the order of the table columns has changed, that could be causing some issues, although it may or may not be the source of the #VALUE! error. However, if the total number of columns in each table has changed, that could be the reason. For example, the SheetMap shows only 13 columns in the Claims table, whereas the sample file contains 14 columns, so using =CHOOSECOLS(tblClaims,14) will return #VALUE! if column 14 no longer exists (out of scope reference).
It would also be helpful if you could provide the column names (table headers) in the SheetMap (it currently only shows the column index numbers). Try using the following formula to generate a complete list, then Copy the results and Paste Special > Values into the SheetMap workbook:
=LET(
i, SEQUENCE(SHEETS(Claims:RESEA!A1)),
j, SEQUENCE(,COLUMNS(A1:Z1)),
HSTACK(TOCOL(IF(j,i)),TOCOL(IF(i,j)),TOCOL(Claims:RESEA!A1:Z1))
)Please include all results, even those that return 0's for fields that don't exist, so I'll be able to easily identify exactly what's been changed (number of columns, column names and order).
Kind regards.
I found a lot of discrepancies. I fixed them and pointed things out best I could. Hopefully this will be able to work. Thank you for the help!
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!
Well, no luck with it. I grabbed a copy of the whole thing again and stripped it of PII. Are you willing to give it one more go? :)