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?
  - clynn8452
    Copper Contributor
    Feb 06, 2023
    JOGINDER SINGH Hello. i need to extract data from and excel spreadsheet to fill out a form that i can customize. i used to do this years ago but cannot recall how i did it. i remember being able to set up the title fields of the date where i wanted them to go on the form. i also need to be able to print the forms.

Forum Discussion

Filling a form with excel function

Share

Resources