Forum Discussion
NotSoFastEddie
Apr 18, 2024Brass Contributor
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 thi...
- Apr 18, 2024
=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
Apr 18, 2024MVP
- NotSoFastEddieApr 19, 2024Brass Contributor
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.
- SergeiBaklanApr 19, 2024MVP
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.