Combine tables

sharshra

Active Member
Joined
Mar 20, 2013
Messages
417
Office Version
  1. 365
I have to combine 2 tables & create a consolidated table to reflect data from both tables. If the names are same, it should add the qty. Otherwise, it should add a new row. Final combined table should be sorted by the name.

I tried using power query merge, but not getting the correct results. Can the experts suggest how this can be done please?

For example consider the following tables.
excel problems.xlsx
CD
2NameQty
3bcd2
4de19
5fghi954
Sheet9


excel problems.xlsx
BC
2NameQty
3abc56
4xyz37
5fghi6
6bcd10
7mnop88
Sheet10


The final combined table should be like this -
excel problems.xlsx
BC
2NameQty
3abc56
4bcd12
5de19
6fghi960
7mnop88
8xyz37
Sheet11
 
Like this.

Book3
ABCDEFGH
1NameQtyNameQtyNameQty
2bcd2abc56bcd12
3de19xyz37de19
4fghi954fghi6fghi960
5bcd10abc56
6mnop88xyz37
7mnop88
Sheet1


Power Query:
let
    Source = Table.Combine({Table1,Table2}),
    Group = Table.Group(Source, {"Name"}, {{"Qty", each List.Sum([Qty]), type number}})
in
    Group
 
Upvote 1
With GROUPBY function:
Excel Formula:
=LET(v,VSTACK(Sheet9!C3:D5,Sheet10!B3:C7),GROUPBY(INDEX(v,,1),INDEX(v,,2),SUM,,0))
 
Upvote 0
Is there any alternative to get the same result?
Try:
Excel Formula:
=LET(v,VSTACK(Sheet9!C3:D5,Sheet10!B3:C7),u,UNIQUE(INDEX(v,,1)),SORT(HSTACK(u,MAP(u,LAMBDA(m,SUM(FILTER(INDEX(v,,2),INDEX(v,,1)=m)))))))
 
Upvote 0
Solution
@sharshra in future please do not mark your post saying it works as the solution. You need to mark the post that actually contains the solution.
 
Upvote 0

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