Forum Discussion
Concatenate matching list of columns into another cell
I am trying to work through this. I have a person enters in data row by row. In the parent row, I need to gather up one of the column entries in the child rows and concatenate them together. I think index/match might be it and working through it. If you have any ideas, help is much appreciated.
=LET(
PCODE_BUNDLEID,
UNIQUE(FILTER(A2:B14,B2:B14<>"")),
IFNA(
REDUCE({"PCODE"."BUNDLEID"."COMPONENTS + QUANTITIES"."Other Stuff"},SEQUENCE(ROWS(PCODE_BUNDLEID)),
LAMBDA(u,v,
VSTACK(u,
HSTACK(INDEX(PCODE_BUNDLEID,v,1),
"",
"",
FILTER(E2:E14,(A2:A14=INDEX(PCODE_BUNDLEID,v,1))*(ISBLANK(B2:B14)) ) ),
HSTACK(INDEX(PCODE_BUNDLEID,v,{1.2}),
TEXTJOIN({"-".","},,FILTER(C2:D14,(A2:A14&B2:B14=INDEX(PCODE_BUNDLEID,v,1)&INDEX(PCODE_BUNDLEID,v,2)))),
FILTER(E2:E14,(A2:A14=INDEX(PCODE_BUNDLEID,v,1))*(B2:B14=INDEX(PCODE_BUNDLEID,v,2))*(ISBLANK(C2:C14))))))),""))
If you work with Office 365 or Excel for the web you can apply this formula which spills the result.
SergeiBaklan thanks very much. I am always amazed at how rich Excel functions are. I will give these a review. Thanks for the time you took to help.
NotSoFastEddie , you are welcome.
That was attempt to play with different techniques, skipping for the moment VBA, Office Script and Python in Excel.
No doubt in each technique (formulae, Power Query, PivotTable) we may generate few different solutions.
What to use depends on concrete scenario.
=LET(
PCODE_BUNDLEID,
UNIQUE(FILTER(A2:B14,B2:B14<>"")),
IFNA(
REDUCE({"PCODE"."BUNDLEID"."COMPONENTS + QUANTITIES"."Other Stuff"},SEQUENCE(ROWS(PCODE_BUNDLEID)),
LAMBDA(u,v,
VSTACK(u,
HSTACK(INDEX(PCODE_BUNDLEID,v,1),
"",
"",
FILTER(E2:E14,(A2:A14=INDEX(PCODE_BUNDLEID,v,1))*(ISBLANK(B2:B14)) ) ),
HSTACK(INDEX(PCODE_BUNDLEID,v,{1.2}),
TEXTJOIN({"-".","},,FILTER(C2:D14,(A2:A14&B2:B14=INDEX(PCODE_BUNDLEID,v,1)&INDEX(PCODE_BUNDLEID,v,2)))),
FILTER(E2:E14,(A2:A14=INDEX(PCODE_BUNDLEID,v,1))*(B2:B14=INDEX(PCODE_BUNDLEID,v,2))*(ISBLANK(C2:C14))))))),""))
If you work with Office 365 or Excel for the web you can apply this formula which spills the result.
- WOW! I would never have ever got this. Thanks for the quick reply and the effort to answer my question.
I am slowly figuring out what you have done and it is some piece of magic. In my version I was receiving a #spill error, but I now have figured out why that was happening.
Thanks again for your effort.
