Combine text from multiple cells into one cell if name matches

[maggiec27]

I want to combine the text for any cell in column C if the name in column A matches. Right now I'm using a simple IF/Text Join formula. This won't work for me. =IF(A4=A3,TEXTJOIN(";",TRUE,B3:B5)) It's too simplistic.

I'd really like to look at all of column A and if there are any matches/duplicates, then text join all values in column C for the first match of all matches. If we can flag the other matches so I can quickly delete them or if there is a macro that could do all of this and delete the subsequent matches that would be great. Is this possible?

Name	Product	Products Combined
Maggie Smith	Duffel Bag	Duffel Bag; Protein Shake; Protein Powder
Karen Jones	Vitamins
Joann Bright	Protein Bar
Maggie Smith	Protein Shake	Flag for deletion or automatically delete
Maggie Smith	Protein Powder	Flag for deletion or automatically delete

<tbody>
</tbody>

 

[Roderick_E]

might be easier to:

1. just use a pivot table
2. rebuild the resulting list -- this way you could use a macro to loop through the name column and in a new name column have the name only once with the Products Combine column appending the product every time the name is duplicated.

 

[sandy666]

another option is PowerQuery aka Get&Transform

Source			Result
Name	Product		Name	Product
Maggie Smith	Duffel Bag		Maggie Smith	Duffel Bag, Protein Shake, Protein Powder
Karen Jones	Vitamins		Karen Jones	Vitamins
Joann Bright	Protein Bar		Joann Bright	Protein Bar
Maggie Smith	Protein Shake
Maggie Smith	Protein Powder

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Product", each List.Distinct(Table.Column([Count],"Product"))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Product", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"[/SIZE]

 

[maggiec27]

another option is PowerQuery aka Get&Transform

Source			Result
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product[/COLOR]		[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product[/COLOR]
Maggie Smith	Duffel Bag		Maggie Smith	Duffel Bag, Protein Shake, Protein Powder
Karen Jones	Vitamins		Karen Jones	Vitamins
Joann Bright	Protein Bar		Joann Bright	Protein Bar
Maggie Smith	Protein Shake
Maggie Smith	Protein Powder

<tbody>
</tbody>

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Product", each List.Distinct(Table.Column([Count],"Product"))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Product", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"[/SIZE]

This is amazing! Thank you so much! Works great!!

 

[sandy666]

You are welcome.
Great if you Like it

 

[maggiec27]

Is there a way to edit that code to include some manipulation on more columns? There will be other columns, such as Order number, quantity returned

• Is there a way to somehow update the 'product column' to say 1-protein, 1-duffel bag if the line that is getting merged says 1 for the protein and 1 for the duffel bag?
• Can we combine the order number separated by semi colons?

Source:

Customer Name	Product	Order Number	Returned Quantity
Maggie Smith	Duffel Bag	1234	1
Karen Jones	Protein Shake	1236	2
Maggie Smith	Protein Bar	1238	2
Maggie Smith	Vitamins	1239	4

<tbody>
</tbody>

Desired:

Customer Name	Product	Order Number	Returned Quantity
Maggie Smith	1-Duffel Bag; 2-Protein Bar; 4-Vitamins	1234;1238;1239
Karen Jones	Protein Shake	1236	2

<tbody>
</tbody>

Is this possible with the Get&Transform tool?

 

[sandy666]

this is a PowerQuery aka Get&Transform

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Customer Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Product", each List.Distinct(Table.Column([Count],"Product"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Order Number", each List.Distinct(Table.Column([Count],"Order Number"))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Returned Quantity", each List.Distinct(Table.Column([Count],"Returned Quantity"))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Count"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Product", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Order Number", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Returned Quantity", each Text.Combine(List.Transform(_, Text.From), "; "), type text})
in
    #"Extracted Values2"[/SIZE]

Customer Name	Product	Order Number	Returned Quantity
Maggie Smith	Duffel Bag; Protein Bar; Vitamins	1234; 1238; 1239	1; 2; 4
Karen Jones	Protein Shake	1236	2

 

[sandy666]

If you want to SUM of Quantity for Maggie and Karen instead of list

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Customer Name"}, {{"Count", each _, type table}, {"Returned Quantity", each List.Sum([Returned Quantity]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Distinct(Table.Column([Count],"Product"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Order Number", each List.Distinct(Table.Column([Count],"Order Number"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Order Number", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values1",{"Count"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Customer Name", "Custom", "Order Number", "Returned Quantity"})
in
    #"Reordered Columns"[/SIZE]

Customer Name	Custom	Order Number	Returned Quantity
Maggie Smith	Duffel Bag; Protein Bar; Vitamins	1234; 1238; 1239	7​
Karen Jones	Protein Shake	1236	2​

 

[sandy666]

More about PowerQuery: here and here

 

[maggiec27]

If the number for returned quantity is the same for Maggie, ie: 1,1,1 the end result is giving me just one 1 in the column instead of 3 separated by semicolons.