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]
 
Sort by date Sort by votes
use
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Returned Quantity", each Table.Column([Count],"Returned Quantity")),
instead
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Returned Quantity", each List.Distinct(Table.Column([Count],"Returned Quantity"))),

you can remove List.Distinct from all custom columns and you will get all values - duplicated or not duplicated
 
Last edited:
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
What can I use in Power Query if the file I receive has SKU numbers for the product name instead of the name? Currently, I was doing an index/match formula manually to find the product name for the sku. I inserted a second tab with the sku numbers and product names and did the index match to that. Then I ran the power query which extracted the product name values. It'd be cool if I could automate that process though and not have to manually add the product name column, fill the formula, then run power query.

I tried updating my power query by clicking add custom column and was thinking I could write a formula in there, but not sure how to use index match and reference my other sheet in the formula box. Maybe there is a different way of doing it in power query?


File with data
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Customer Name
[/TD]
[TD]SKU
[/TD]
[TD]Helper column for Index/Match formula
[/TD]
[/TR]
[TR]
[TD]Maggie
[/TD]
[TD]1001
[/TD]
[TD]=index(match.......) If SKU = 1001, value here is Duffel Bag
[/TD]
[/TR]
</tbody>[/TABLE]






Master Product/SKU Data (Separate file adding in as an extra tab currently but open to something different)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]1001
[/TD]
[TD]Duffel Bag
[/TD]
[/TR]
[TR]
[TD]1002
[/TD]
[TD]Vitamins
[/TD]
[/TR]
[TR]
[TD]1003
[/TD]
[TD]Protein Shake
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Forget about vba or formulas with Poweruery. Change way of thinking.

ad rem:
you've two table
Tb1: customer and sku
Tb2: sku and product

first you need connect tb2 to tb1 by sku then you can do what you want with the result, eg. post#7
with you example from post#12
add table1 to PQ
add table2 to PQ
merge these tables by SKU
Code: 
[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"SKU"},Table2,{"SKU"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Product"}, {"Product"})
in
    #"Expanded Table2"[/SIZE]


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

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Maggie[/td][td=bgcolor:#DDEBF7]
1001​
[/td][td][/td][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Duffel Bag[/td][td][/td][td=bgcolor:#E2EFDA]Maggie[/td][td=bgcolor:#E2EFDA]
1001​
[/td][td=bgcolor:#E2EFDA]Duffel Bag[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]
1002​
[/td][td]Vitamins[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]
1003​
[/td][td=bgcolor:#DDEBF7]Protein Shake[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

or I missed something?

edit:
add more representative examples with five or more rows
 
Last edited:
Upvote 0
This is my query but I'm getting a cyclic reference error: The query worked until I tried editing the source.

let
Source = Table.NestedJoin(Table1,{"SKU"},Table2,{"SKU"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Product"}, {"Product"}),
#"Grouped Rows" = Table.Group(Source, {"Customer Name"}, {{"Count", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Product", each Table.Column([Count],"Product")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Order Number", each Table.Column([Count],"Order Number")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Returned Quantity", each Table.Column([Count],"Quantity Returned")),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Shipping Address", each List.Distinct(Table.Column([Count],"Address1"))),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Shipping Primary Reason", each List.Distinct(Table.Column([Count],"Reason For Return"))),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Shipping Zipcode", each List.Distinct(Table.Column([Count],"Zip"))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"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}),
#"Extracted Values3" = Table.TransformColumns(#"Extracted Values2", {"Shipping Address", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Extracted Values4" = Table.TransformColumns(#"Extracted Values3", {"Shipping Primary Reason", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Extracted Values5" = Table.TransformColumns(#"Extracted Values4", {"Shipping Zipcode", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Added Custom7" = Table.AddColumn(#"Extracted Values5", "Owner ID", each "00G40000002JXdb"),
#"Added Custom6" = Table.AddColumn(#"Added Custom7", "Case Origin", each "Returns Log"),
#"Added Custom8" = Table.AddColumn(#"Added Custom6", "Record Type ID", each "01240000000Ue13")
in
#"Added Custom8"

Table 1: Customer and Return Details

[TABLE="class: grid, width: 984, align: left"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]Customer Name[/TD]
[TD]Address1[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD]Reason For Return[/TD]
[TD]Quantity Returned[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD="align: right"]99323462[/TD]
[TD]Maggie Curry[/TD]
[TD]1234 Happy Street[/TD]
[TD]Dallas[/TD]
[TD]TX[/TD]
[TD]75212[/TD]
[TD]RTS - Return To Sender[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1001[/TD]
[/TR]
[TR]
[TD="align: right"]99323462[/TD]
[TD]Maggie Curry[/TD]
[TD]1235 Happy Street[/TD]
[TD]Dallas[/TD]
[TD]TX[/TD]
[TD]75212[/TD]
[TD]RTS - Return To Sender[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1002[/TD]
[/TR]
[TR]
[TD="align: right"]99323463[/TD]
[TD]Karen Jones[/TD]
[TD]1236 Jones Road[/TD]
[TD]Addison[/TD]
[TD]Tx[/TD]
[TD]75213[/TD]
[TD]RTS - Return To Sender[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1003[/TD]
[/TR]
[TR]
[TD="align: right"]99323464[/TD]
[TD]Ben Smith[/TD]
[TD]1237 Willow Way[/TD]
[TD]Plano[/TD]
[TD]TX[/TD]
[TD]75214[/TD]
[TD]RTS - Return To Sender[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD="align: right"]99323465[/TD]
[TD]Maggie Curry[/TD]
[TD]1238 Happy Street[/TD]
[TD]Dallas[/TD]
[TD]TX[/TD]
[TD]75212[/TD]
[TD]RTS - Return To Sender[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1006[/TD]
[/TR]
</tbody>[/TABLE]












Table 2:

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD="width: 64"]SKU[/TD]
[TD="width: 264"]Product[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Duffel Bags[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Protein Shake[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Vitamins[/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]Blender Bottle[/TD]
[/TR]
[TR]
[TD]1006[/TD]
[TD]T-Shirt[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Because you didn't show expected result here is merged and grouped by Customer Name result:
(use CODE tags if you posting any code)
Code: 
[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"SKU"},Table2,{"SKU"},"Table2",JoinKind.LeftOuter),
    #"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]

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Order Number[/td][td=bgcolor:#5B9BD5]Customer Name[/td][td=bgcolor:#5B9BD5]Address1[/td][td=bgcolor:#5B9BD5]City[/td][td=bgcolor:#5B9BD5]State[/td][td=bgcolor:#5B9BD5]Zip[/td][td=bgcolor:#5B9BD5]Reason For Return[/td][td=bgcolor:#5B9BD5]Quantity Returned[/td][td=bgcolor:#5B9BD5]SKU[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
99323462​
[/td][td=bgcolor:#DDEBF7]Maggie Curry[/td][td=bgcolor:#DDEBF7]1234 Happy Street[/td][td=bgcolor:#DDEBF7]Dallas[/td][td=bgcolor:#DDEBF7]TX[/td][td=bgcolor:#DDEBF7]
75212​
[/td][td=bgcolor:#DDEBF7]RTS - Return To Sender[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
1001​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
99323462​
[/td][td]Maggie Curry[/td][td]1235 Happy Street[/td][td]Dallas[/td][td]TX[/td][td]
75212​
[/td][td]RTS - Return To Sender[/td][td]
2​
[/td][td]
1002​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
99323463​
[/td][td=bgcolor:#DDEBF7]Karen Jones[/td][td=bgcolor:#DDEBF7]1236 Jones Road[/td][td=bgcolor:#DDEBF7]Addison[/td][td=bgcolor:#DDEBF7]Tx[/td][td=bgcolor:#DDEBF7]
75213​
[/td][td=bgcolor:#DDEBF7]RTS - Return To Sender[/td][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]
1003​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
99323464​
[/td][td]Ben Smith[/td][td]1237 Willow Way[/td][td]Plano[/td][td]TX[/td][td]
75214​
[/td][td]RTS - Return To Sender[/td][td]
1​
[/td][td]
105​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
99323465​
[/td][td=bgcolor:#DDEBF7]Maggie Curry[/td][td=bgcolor:#DDEBF7]1238 Happy Street[/td][td=bgcolor:#DDEBF7]Dallas[/td][td=bgcolor:#DDEBF7]TX[/td][td=bgcolor:#DDEBF7]
75212​
[/td][td=bgcolor:#DDEBF7]RTS - Return To Sender[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
1006​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Table2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]SKU[/td][td=bgcolor:#5B9BD5]Product[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Duffel Bags[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1002​
[/td][td]Protein Shake[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1003​
[/td][td=bgcolor:#DDEBF7]Vitamins[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1005​
[/td][td]Blender Bottle[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1006​
[/td][td=bgcolor:#DDEBF7]T-Shirt[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Result[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Customer Name[/td][td=bgcolor:#70AD47]Order Number[/td][td=bgcolor:#70AD47]Address1[/td][td=bgcolor:#70AD47]City[/td][td=bgcolor:#70AD47]State[/td][td=bgcolor:#70AD47]Zip[/td][td=bgcolor:#70AD47]Reason For Return[/td][td=bgcolor:#70AD47]Quatity Returned[/td][td=bgcolor:#70AD47]SKU[/td][td=bgcolor:#70AD47]Product[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Maggie Curry[/td][td=bgcolor:#E2EFDA]99323462; 99323462; 99323465[/td][td=bgcolor:#E2EFDA]1234 Happy Street; 1235 Happy Street; 1238 Happy Street[/td][td=bgcolor:#E2EFDA]Dallas; Dallas; Dallas[/td][td=bgcolor:#E2EFDA]TX; TX; TX[/td][td=bgcolor:#E2EFDA]75212; 75212; 75212[/td][td=bgcolor:#E2EFDA]RTS - Return To Sender; RTS - Return To Sender; RTS - Return To Sender[/td][td=bgcolor:#E2EFDA]1; 2; 1[/td][td=bgcolor:#E2EFDA]1001; 1002; 1006[/td][td=bgcolor:#E2EFDA]Duffel Bags; Protein Shake; T-Shirt[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Karen Jones[/td][td]99323463[/td][td]1236 Jones Road[/td][td]Addison[/td][td]Tx[/td][td]75213[/td][td]RTS - Return To Sender[/td][td]3[/td][td]1003[/td][td]Vitamins[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ben Smith[/td][td=bgcolor:#E2EFDA]99323464[/td][td=bgcolor:#E2EFDA]1237 Willow Way[/td][td=bgcolor:#E2EFDA]Plano[/td][td=bgcolor:#E2EFDA]TX[/td][td=bgcolor:#E2EFDA]75214[/td][td=bgcolor:#E2EFDA]RTS - Return To Sender[/td][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]105[/td][td=bgcolor:#E2EFDA][/td][/tr]
[/table]
You can change headers name in a last step if you want
and you can add List.Distinct there where you want
all above is an example of solution
 
Last edited:
Upvote 0
No!, of course not. You can move (drag) every column of source there where you want but before Table1 will be loaded into PQ
or move any column after load into PQ in PQ editor
 
Last edited:
Upvote 0
I moved SKU of Table1 from the end to the second position and as you can see result didn't change

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Order Number[/td][td=bgcolor:#5B9BD5]SKU[/td][td=bgcolor:#5B9BD5]Customer Name[/td][td=bgcolor:#5B9BD5]Address1[/td][td=bgcolor:#5B9BD5]City[/td][td=bgcolor:#5B9BD5]State[/td][td=bgcolor:#5B9BD5]Zip[/td][td=bgcolor:#5B9BD5]Reason For Return[/td][td=bgcolor:#5B9BD5]Quantity Returned[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
99323462​
[/td][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Maggie Curry[/td][td=bgcolor:#DDEBF7]1234 Happy Street[/td][td=bgcolor:#DDEBF7]Dallas[/td][td=bgcolor:#DDEBF7]TX[/td][td=bgcolor:#DDEBF7]
75212​
[/td][td=bgcolor:#DDEBF7]RTS - Return To Sender[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
99323462​
[/td][td]
1002​
[/td][td]Maggie Curry[/td][td]1235 Happy Street[/td][td]Dallas[/td][td]TX[/td][td]
75212​
[/td][td]RTS - Return To Sender[/td][td]
2​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
99323463​
[/td][td=bgcolor:#DDEBF7]
1003​
[/td][td=bgcolor:#DDEBF7]Karen Jones[/td][td=bgcolor:#DDEBF7]1236 Jones Road[/td][td=bgcolor:#DDEBF7]Addison[/td][td=bgcolor:#DDEBF7]Tx[/td][td=bgcolor:#DDEBF7]
75213​
[/td][td=bgcolor:#DDEBF7]RTS - Return To Sender[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
99323464​
[/td][td]
105​
[/td][td]Ben Smith[/td][td]1237 Willow Way[/td][td]Plano[/td][td]TX[/td][td]
75214​
[/td][td]RTS - Return To Sender[/td][td]
1​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
99323465​
[/td][td=bgcolor:#DDEBF7]
1006​
[/td][td=bgcolor:#DDEBF7]Maggie Curry[/td][td=bgcolor:#DDEBF7]1238 Happy Street[/td][td=bgcolor:#DDEBF7]Dallas[/td][td=bgcolor:#DDEBF7]TX[/td][td=bgcolor:#DDEBF7]
75212​
[/td][td=bgcolor:#DDEBF7]RTS - Return To Sender[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Table2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]SKU[/td][td=bgcolor:#5B9BD5]Product[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Duffel Bags[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1002​
[/td][td]Protein Shake[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1003​
[/td][td=bgcolor:#DDEBF7]Vitamins[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1005​
[/td][td]Blender Bottle[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1006​
[/td][td=bgcolor:#DDEBF7]T-Shirt[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Result[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Customer Name[/td][td=bgcolor:#70AD47]Order Number[/td][td=bgcolor:#70AD47]Address1[/td][td=bgcolor:#70AD47]City[/td][td=bgcolor:#70AD47]State[/td][td=bgcolor:#70AD47]Zip[/td][td=bgcolor:#70AD47]Reason For Return[/td][td=bgcolor:#70AD47]Quatity Returned[/td][td=bgcolor:#70AD47]SKU[/td][td=bgcolor:#70AD47]Product[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Maggie Curry[/td][td=bgcolor:#E2EFDA]99323462; 99323462; 99323465[/td][td=bgcolor:#E2EFDA]1234 Happy Street; 1235 Happy Street; 1238 Happy Street[/td][td=bgcolor:#E2EFDA]Dallas; Dallas; Dallas[/td][td=bgcolor:#E2EFDA]TX; TX; TX[/td][td=bgcolor:#E2EFDA]75212; 75212; 75212[/td][td=bgcolor:#E2EFDA]RTS - Return To Sender; RTS - Return To Sender; RTS - Return To Sender[/td][td=bgcolor:#E2EFDA]1; 2; 1[/td][td=bgcolor:#E2EFDA]1001; 1002; 1006[/td][td=bgcolor:#E2EFDA]Duffel Bags; Protein Shake; T-Shirt[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Karen Jones[/td][td]99323463[/td][td]1236 Jones Road[/td][td]Addison[/td][td]Tx[/td][td]75213[/td][td]RTS - Return To Sender[/td][td]3[/td][td]1003[/td][td]Vitamins[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ben Smith[/td][td=bgcolor:#E2EFDA]99323464[/td][td=bgcolor:#E2EFDA]1237 Willow Way[/td][td=bgcolor:#E2EFDA]Plano[/td][td=bgcolor:#E2EFDA]TX[/td][td=bgcolor:#E2EFDA]75214[/td][td=bgcolor:#E2EFDA]RTS - Return To Sender[/td][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]105[/td][td=bgcolor:#E2EFDA][/td][/tr]
[/table]
 
Upvote 0
Looks good. I was having issues with setting up the two tables and creating the merge. Good practice and learned alot doing it. But I finally got it the table working as you have described. I replicated each step manually to get a feel for how the editor works and used your code as a reference. Get and Transform is a really cool feature, incredibly useful. Right now I have just been practicing with some sample data, so I've just been using a small sample of data on my current workbook.

In the future, I will receive a new file daily with all of the return information while the SKU and product name file stays the same. What is the best way to go about transforming the data?

Do I save this workbook I've been using and from it, 'get data', get from file, then load my query or is that going to screw everything up since I did all of this test work using 'tables'?
 
Upvote 0
Table1 = will be changed
Table2 = unchanged

new table append (not append as new!) to the table1, refresh result table (theory - because I don't know real structure of your data)

you can load tables from the same workbook (sheets), from other workbooks.... there is many sources. Check NewQuery option.

edit:
all steps are on the right side of the PQ Editor, with a little gear on the end of almost every step, where you can see sub-steps

btw. merging tables is like vlookup or index/match :)
 
Last edited:
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,792
Messages
6,180,984
Members
453,011
Latest member
Osamu9Dazai

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