concatenation - How can I concatenate multiple cells from the same column and different lines in Excel?

Question

I have 3 columns of data and need to concatenate two values of the same column, but are grouped by a separate column (Column A). In this example below, I need to combine the Brand + Product Type (column C) for a total of 66 concatenated values.

How can I get the concatenated values I need? There's thousands that I need to combine.

ABC Diffusers
ABC Hot & Cold Therapy
ABC Humidifiers
DEF Oils
DEF Diffusers
DEF Candles
DEF Air Fresheners
etc.

question from:https://stackoverflow.com/questions/65864021/how-can-i-concatenate-multiple-cells-from-the-same-column-and-different-lines-in

Answer 1

This solution works for versions of Excel that support LET and dynamic arrays

I cut you data down a little to save space but this should give you an idea.

The yellow cells have formulas, the rest is spilled dynamic arrays.

G2 lists the unique titles horizontally. =TRANSPOSE(UNIQUE(A3:A12))

G3 lists all of the diffuser combos.

=LET(titleList,$A$3:$A$12,
     groupList,$B$3:$B$12,
     nameList,$C$3:$C$12,
     Title,G2,
     brandList,TRANSPOSE(FILTER(nameList,(titleList=Title)*(groupList="Brand"))),
     productType,FILTER(nameList,(titleList=Title)*(groupList="Product Type")),
     combos,brandList&" "&productType,
     numRows,ROWS(combos),
     numCols,COLUMNS(combos),
     outRows,SEQUENCE(numRows*numCols),
     
     INDEX(combos,MOD(outRows-1,numRows)+1,INT((outRows-1)/numRows)))

H3 lists all of essential-oils combos. It is G3 copied. A1 shows the max length of the combos for each Title. =MAX(COUNTIFS($A$3:$A$12,G2#,$B$3:$B$12,"Brand")*COUNTIFS($A$3:$A$12,G2#,$B$3:$B$12,"Product Type"))

E3 lists all of the unique combos.

=LET(numRows,A1,
     numCols,COLUMNS(G2#),
     data,OFFSET(G3,0,0,numRows,numCols),
     outRows,SEQUENCE(numRows*numCols),
     oneCol,INDEX(data,MOD(outRows-1,numRows)+1,INT((outRows-1)/numRows)+1),

     FILTER(oneCol,oneCol<>0))

More info on LET More info on dynamic arrays