show difference for two tabs with the same headers

[david gabra]

Hi
I have 2 tabs
One for November data
One for December data
They have common headers.
I need to show the differences between the December data and the November data for each column for each employee - so to add the Nov balances and difference column in the Dec tab.
Employee number is the unique key and is can be found on the first column.
For me to do this manual will be inefficient and so much time.
Can someone help with excel logic to assist me automate this.
Thanks
David.

 

[AlanSidman]

1. Copy Row 1 from December to new sheet
2. Copy Column A from December to new sheet
3. In B2 type ='30DEC'!B2-'Nov 30'!B2
4. In new sheet copy B2 down and across.
Worksheet Attached

 

[p45cal]

Two offerings in the attached, both Power Queries:
1.Table at cell A22 of sheet 30DEC. The headers of this table are the same as the headers in row 1 of that sheet but the cell contents are as follows:
If no change between the 2 tables then just the value is placed there.
If there is a change then it shows 30DECValue (Nov 30Value) [Difference] in one cell.
The problem with this format is that you can't do arithmetic on the cells with multiple values in.


2. Table at cell A43 of sheet 30DEC. The headers of this table is each header in row 1 of that sheet split into 3 headers eg. SD,SD.last,SD.diff


To update these tables right-click a cell in that table and choose Refresh (alternatively, click the Refresh All icon in the Queries & Connections section of the Data tab of the ribbon).

 

[david gabra]

Two offerings in the attached, both Power Queries:
1.Table at cell A22 of sheet 30DEC. The headers of this table are the same as the headers in row 1 of that sheet but the cell contents are as follows:
If no change between the 2 tables then just the value is placed there.
If there is a change then it shows 30DECValue (Nov 30Value) [Difference] in one cell.
The problem with this format is that you can't do arithmetic on the cells with multiple values in.
View attachment 73427

2. Table at cell A43 of sheet 30DEC. The headers of this table is each header in row 1 of that sheet split into 3 headers eg. SD,SD.last,SD.diff
View attachment 73429

To update these tables right-click a cell in that table and choose Refresh (alternatively, click the Refresh All icon in the Queries & Connections section of the Data tab of the ribbon).

This is excellent - but what happens if my source data has more than 18 lines.

How do I add more lines to the source data so that the pivot table will work.

I tried everything and nothing works.

 

[p45cal]

I only put the two result tables on the same sheet as the more recent source data to make it easy to compare. You can select each result table in its entirety then cut and paste to a clean sheet . Make sure the new data is completely included in the current table, for both source data ranges. You could delete the tables and replace them with your own, all you need to ensure is that the two tables are named the same as the current ones.

 

[david gabra]

I only put the two result tables on the same sheet as the more recent source data to make it easy to compare. You can select each result table in its entirety then cut and paste to a clean sheet . Make sure the new data is completely included in the current table, for both source data ranges. You could delete the tables and replace them with your own, all you need to ensure is that the two tables are named the same as the current ones.

awesome!!!!
thanks it works