How to Sum two Excel Files

[Hongli1216]

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

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

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

[echuong1 · Accepted solution]

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

[Hongli1216]

Hi Echuong,

It's awesome! Thanks for your solution! Save me a lot time!