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?

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

 

[maggiec27]

Table 1 = will be changed
Table 2 = unchanged

When I append, it just adds new data onto my existing table 1. I want to replace that data. Is this expected?

 

[sandy666]

Append newTable to Table1 will give you all data under name Table1 (easier for later use)
then you can do what you want but structure should be the same as before for merging with Table2

eg.
Table1
NewTableA
NewTableB
etc...
all tables should have the same structure as Table1

after Append you can remove duplicated rows and Merge with Table2

all depends what you want to achieve.

example is the best way to show what you've and what you want (sources and expected result)

if you don't want Table1 as source but NewTableA simply use NewTableA as table to merge with Table2

 

[sandy666]

new table as Table1
NewTableA should be loaded into PQ of course

[SIZE=1]let
    Source = Table.NestedJoin([COLOR="#FF0000"][B]Table1[/B][/COLOR],{"SKU"},Table2,{"SKU"},"Table2",JoinKind.LeftOuter), //[COLOR="#FF0000"]if you want use [B]NewTableA[/B] as source change the red name to new tablwe name, eg. [B]NewTableA[/B]. I assume all headers are the same and number of columns are the same[/COLOR]
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Product"}, {"Product"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table2", {"Customer Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Order Number", each Table.Column([Count],"Order Number")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Order Number", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Address1", each Table.Column([Count],"Address1")),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Address1", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values1", "City", each Table.Column([Count],"City")),
    #"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"City", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom3" = Table.AddColumn(#"Extracted Values2", "State", each Table.Column([Count],"State")),
    #"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"State", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom4" = Table.AddColumn(#"Extracted Values3", "Zip", each Table.Column([Count],"Zip")),
    #"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"Zip", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom5" = Table.AddColumn(#"Extracted Values4", "Reason For Return", each Table.Column([Count],"Reason For Return")),
    #"Extracted Values5" = Table.TransformColumns(#"Added Custom5", {"Reason For Return", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom6" = Table.AddColumn(#"Extracted Values5", "Quatity Returned", each Table.Column([Count],"Quantity Returned")),
    #"Extracted Values6" = Table.TransformColumns(#"Added Custom6", {"Quatity Returned", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom7" = Table.AddColumn(#"Extracted Values6", "SKU", each Table.Column([Count],"SKU")),
    #"Extracted Values7" = Table.TransformColumns(#"Added Custom7", {"SKU", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom8" = Table.AddColumn(#"Extracted Values7", "Product", each Table.Column([Count],"Product")),
    #"Extracted Values8" = Table.TransformColumns(#"Added Custom8", {"Product", each Text.Combine(List.Transform(_, Text.From), "; "), type text})
in
    #"Extracted Values8"[/SIZE]

 

[sandy666]

here is a file how to change source and stay with the same name Table1 Link
I changed SKU only but all other data can be different also - except headers and number of columns
There is a few more ways to achieve this

 

[maggiec27]

It seems like that will be the best for my purpose as I'll be getting a new file every day. I am just adding it to my existing worksheet, merging the new table with table2 and running the merge query again. Thank you so much for your help!

 

[sandy666]

Honestly I don't know what you want to achieve but if it is work for you that's fine
Have a nice day
ps.
you can remove old Table1, add new table and name it Table1 and it should work also