Hello everyone. I need help of your's. Can you Please? I have attached an excel sheet for your reference. So please open that and help to resolve my problem mentioned therein

JOGINDER SINGH It isn't "pretty" but I believe this does what you need:=LET(Data,A6:B12, mains, J6:L6, Flavours, I7:I14, _m, TAKE(Data,,1), _s, TAKE(Data,,2), MAKEARRAY(ROWS(Flavours),COLUMNS(mains),LAMBDA(r,c,SUM(IFERROR(--(TEXTBEFORE(TEXTSPLIT(","&TEXTJOIN(",",1,FILTER(_s,_m=INDEX(mains,c),"")),INDEX(Flavours,r)&"/",,1,1,""),",",,1,1,"")),0)))))

Thanks for giving your valuable time, But can you tell me that in which cell address this function to be apply?

did you open the attachment? I re-created the headers (left and top) and then that formula is in the upper left corner of the grid. The "Data" variable is set to A6:B12 where the original data set is and then the "mains" variable points to J6:L6 where I copied the header row and "Falvours" points to I7:I14 where I copied the left column. "point" those 3 variable to the correct corresponding parts and the formula will do the rest.You could also use UNIQUE and such to create the header and left column dynamically but was sure you needed/wanted that part.

Dear, Thanks a lot for making efforts for me, but I am still unable to calculate that. Because I am beginner in MS Excel and still not able to use complex formulas. So please calculate that at your end and please send me calculated sheet. I will try to learn from the formulas you will fix in my sheet. Please

Filling a form with excel function | Microsoft Community Hub

mtarler
Silver Contributor
Feb 03, 2023
JOGINDER SINGH It isn't "pretty" but I believe this does what you need:
=LET(Data,A6:B12, mains, J6:L6, Flavours, I7:I14, _m, TAKE(Data,,1), _s, TAKE(Data,,2), MAKEARRAY(ROWS(Flavours),COLUMNS(mains),LAMBDA(r,c,SUM(IFERROR(--(TEXTBEFORE(TEXTSPLIT(","&TEXTJOIN(",",1,FILTER(_s,_m=INDEX(mains,c),"")),INDEX(Flavours,r)&"/",,1,1,""),",",,1,1,"")),0)))))
ListofPurchaseVouchers.xlsx13 KB
- JOGINDER SINGH
  Copper Contributor
  Feb 04, 2023
  Thanks for giving your valuable time, But can you tell me that in which cell address this function to be apply?
  - mtarler
    Silver Contributor
    Feb 04, 2023
    did you open the attachment? I re-created the headers (left and top) and then that formula is in the upper left corner of the grid. The "Data" variable is set to A6:B12 where the original data set is and then the "mains" variable points to J6:L6 where I copied the header row and "Falvours" points to I7:I14 where I copied the left column. "point" those 3 variable to the correct corresponding parts and the formula will do the rest.
    You could also use UNIQUE and such to create the header and left column dynamically but was sure you needed/wanted that part.

Forum Discussion

Filling a form with excel function

Resources