Combine text from multiple cells into one cell if name matches

M

maggiec27

Board Regular
Joined
Dec 11, 2013
Messages
55
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?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Product
[/TD]
[TD]Products Combined
[/TD]
[/TR]
[TR]
[TD]Maggie Smith
[/TD]
[TD]Duffel Bag
[/TD]
[TD]Duffel Bag; Protein Shake; Protein Powder
[/TD]
[/TR]
[TR]
[TD]Karen Jones
[/TD]
[TD]Vitamins
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joann Bright
[/TD]
[TD]Protein Bar
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Maggie Smith
[/TD]
[TD]Protein Shake
[/TD]
[TD]Flag for deletion or automatically delete
[/TD]
[/TR]
[TR]
[TD]Maggie Smith
[/TD]
[TD]Protein Powder
[/TD]
[TD]Flag for deletion or automatically delete
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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.
 
Upvote 0
another option is PowerQuery aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Source[/td][td][/td][td][/td][td]Result[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Product[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Product[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Maggie Smith[/td][td=bgcolor:#DDEBF7]Duffel Bag[/td][td][/td][td=bgcolor:#E2EFDA]Maggie Smith[/td][td=bgcolor:#E2EFDA]Duffel Bag, Protein Shake, Protein Powder[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Karen Jones[/td][td]Vitamins[/td][td][/td][td]Karen Jones[/td][td]Vitamins[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Joann Bright[/td][td=bgcolor:#DDEBF7]Protein Bar[/td][td][/td][td=bgcolor:#E2EFDA]Joann Bright[/td][td=bgcolor:#E2EFDA]Protein Bar[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Maggie Smith[/td][td]Protein Shake[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Maggie Smith[/td][td=bgcolor:#DDEBF7]Protein Powder[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[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]
 
Upvote 0
sandy666 said:
another option is PowerQuery aka Get&Transform

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Source[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Maggie Smith[/TD]
[TD="bgcolor: #DDEBF7"]Duffel Bag[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]Maggie Smith[/TD]
[TD="bgcolor: #E2EFDA"]Duffel Bag, Protein Shake, Protein Powder[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Karen Jones[/TD]
[TD]Vitamins[/TD]
[TD][/TD]
[TD]Karen Jones[/TD]
[TD]Vitamins[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Joann Bright[/TD]
[TD="bgcolor: #DDEBF7"]Protein Bar[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]Joann Bright[/TD]
[TD="bgcolor: #E2EFDA"]Protein Bar[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Maggie Smith[/TD]
[TD]Protein Shake[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Maggie Smith[/TD]
[TD="bgcolor: #DDEBF7"]Protein Powder[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Code: 
[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]
Click to expand...



This is amazing! Thank you so much! Works great!!
 
Upvote 0
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:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Customer Name
[/TD]
[TD]Product
[/TD]
[TD]Order Number
[/TD]
[TD]Returned Quantity
[/TD]
[/TR]
[TR]
[TD]Maggie Smith
[/TD]
[TD]Duffel Bag
[/TD]
[TD]1234
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Karen Jones
[/TD]
[TD]Protein Shake
[/TD]
[TD]1236
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Maggie Smith
[/TD]
[TD]Protein Bar
[/TD]
[TD]1238
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Maggie Smith
[/TD]
[TD]Vitamins
[/TD]
[TD]1239
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]









Desired:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Customer Name
[/TD]
[TD]Product
[/TD]
[TD]Order Number
[/TD]
[TD]Returned Quantity
[/TD]
[/TR]
[TR]
[TD]Maggie Smith
[/TD]
[TD]1-Duffel Bag; 2-Protein Bar; 4-Vitamins
[/TD]
[TD]1234;1238;1239
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Karen Jones
[/TD]
[TD]Protein Shake
[/TD]
[TD]1236
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]








Is this possible with the Get&Transform tool?
 
Upvote 0
this is a PowerQuery aka Get&Transform :)
Code: 
[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]


[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Customer Name[/td][td=bgcolor:#70AD47]Product[/td][td=bgcolor:#70AD47]Order Number[/td][td=bgcolor:#70AD47]Returned Quantity[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Maggie Smith[/td][td=bgcolor:#E2EFDA]Duffel Bag; Protein Bar; Vitamins[/td][td=bgcolor:#E2EFDA]1234; 1238; 1239[/td][td=bgcolor:#E2EFDA]1; 2; 4[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Karen Jones[/td][td]Protein Shake[/td][td]1236[/td][td]2[/td][/tr]
[/table]
 
Last edited:
Upvote 0
If you want to SUM of Quantity for Maggie and Karen instead of list

Code: 
[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]


[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Customer Name[/td][td=bgcolor:#70AD47]Custom[/td][td=bgcolor:#70AD47]Order Number[/td][td=bgcolor:#70AD47]Returned Quantity[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Maggie Smith[/td][td=bgcolor:#E2EFDA]Duffel Bag; Protein Bar; Vitamins[/td][td=bgcolor:#E2EFDA]1234; 1238; 1239[/td][td=bgcolor:#E2EFDA]
7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Karen Jones[/td][td]Protein Shake[/td][td]1236[/td][td]
2​
[/td][/tr]
[/table]
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Filter and Textjoin formula if two columns match a text value in a cell - excel 365 in a Mac
Replies
7
Views
367
Fluff
Fluff
V
  • Solved
Show duplicates from across multiple ranges in one cell with a single nested formula?
Replies
9
Views
186
VikingLink
V
L
  • Question Question
VBA code to look in multiple sheets, multiple columns, delete rows from one sheet
Replies
9
Views
1K
jolivanes
jolivanes
N
  • Solved
Separate One Cell Data into Multiple Rows
Replies
6
Views
1K
Fluff
Fluff
P
  • Question Question
Deleting repeating text from a column where each cell in the column has multiple text phrases
Replies
2
Views
382
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,785
Messages
6,180,945
Members
453,007
Latest member
anaysha1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top