I have two Excel files with the exact same formats. How to add the table 1 to table 2 to get table 3. I really appreciate your help! Thanks! Table 1 Table 2 Table 3 ItemsALAK ItemsALAK ItemsALAKSales 100 200 Sales 200 300 Sales 300 500Gain 300 400 Gain 400 600 Gain 700 1,000Mis Income 200 300 Mis Income 500 600 Mis Income 700 900 Holly

How to Sum two Excel Files

I have two Excel files with the exact same formats. How to add the table 1 to table 2 to get table 3. I really appreciate your help! Thanks!

Table 1			Table 2			Table 3
Items	AL	AK	Items	AL	AK	Items	AL	AK
Sales	100	200	Sales	200	300	Sales	300	500
Gain	300	400	Gain	400	600	Gain	700	1,000
Mis Income	200	300	Mis Income	500	600	Mis Income	700	900

Holly

Common Use Cases

Accepted answers

echuong1

Yes! You can add a record ID to keep the original order.

New Workflow9.yxmd

All comments

echuong1

I'd start off by creating a consolidated list of all of your records. You can do this dynamically with a wildcard in your input, or just use a union tool. After that, you can pivot your data using transpose to get all values in one column, then pivot back using the cross tab. The crosstab can be configured to automatically sum all values where they have the same header and item name.

You could also do a join with both tables, but then you'd need to create the sum for each column individually. The way I've laid out is dynamic and will handle any number of columns.

Hope this helps!

New Workflow9.yxmd

Hongli1216

Hi Echuong,

Thanks for your quick response! The value added correctly. I'm wondering whether the Items column can keep the original order. Right now the output changed the order of Items in ascending. Please advise!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703