SOLVED

# Concatenate matching list of columns into another cell

Brass 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 think index/match might be it and working through it.  If you have any ideas, help is much appreciated.

6 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Concatenate matching list of columns into another cell

=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.

# Re: Concatenate matching list of columns into another cell

WOW! I would never have ever got this. Thanks for the quick reply and the effort to answer my question.

# Re: Concatenate matching list of columns into another cell

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.

# Re: Concatenate matching list of columns into another cell

Couple of more variants

# Re: Concatenate matching list of columns into another cell

@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.

# Re: Concatenate matching list of columns into another cell

@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.

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Concatenate matching list of columns into another cell

=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.