Smarter way to Compare 2 excel tabs?

kevdragon1

New Member
Joined
Mar 8, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have software that generates reports. Part of my job is to see the difference between these 2 reports. So I am basically trying to compare version 1 to version 2.

I was wondering if there was a smart way that I can compare these reports. What I would like would be to have 3 tabs in the excel file. Tab 1 has Version 1, tab 2 has Version 2 and tab 3 is comparing both tabs.

Here is an example:

Version 1 :
Balance sheetDec 2020Jan 2021
Asset 110001500
Asset 230003500

Version 2:
Balance sheetDec 2020Jan 2021
Asset 115001000
Asset 230000
Asset 350006000


Compare:
Balance sheetDec 2020Jan 2021
Asset 1500-500
Asset 20-3500
Asset 350006000

Something like this.

As you can see the report can sometimes have different lines from version to version that was not included before (asset 3). Sometimes some "Balance sheet" items will disappear aslo.

The way I solved this was to copy the "Balance sheet" items from the most recent version (version 2) and then use an XLOOKUP to find the same items in the previous version. Then I would do a difference to see the impact.

I have reports going for 10 years....so there is a lot of formulas and I am posting because I wanted to see if there was a better way to do this.

Thx
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,291
Office Version
  1. 365
Platform
  1. Windows
Power Query would be ideal for this.
Below is Jeff Lenning (Excel University) with a 4.5 min video that will give you the idea of what you could do.
Since you want to show all values from both versions you will want to use a Full Outer Join.


The Explanatory web page for the Video is:
Slow to Fast 3 - Excel University
 

kevdragon1

New Member
Joined
Mar 8, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Power Query would be ideal for this.
Below is Jeff Lenning (Excel University) with a 4.5 min video that will give you the idea of what you could do.
Since you want to show all values from both versions you will want to use a Full Outer Join.


The Explanatory web page for the Video is:
Slow to Fast 3 - Excel University
thank you... this worked partially. I was able to merge the 2 tables and I actually had all the lines from both versions. How do I do the difference between 2 merged columns?

Also with 2 tables and connections, this seems overkill. Isn't there an easier way?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,291
Office Version
  1. 365
Platform
  1. Windows
You can combine the queries to make it into a single query but it would most likely mean you have to modify the actual M Code.

I took a slightly different approach using Append rather than Merge.
For the below to work unmodified you would need to make sure your headings are the same in both Versions

To use the below
• Give the tables the same name as I have.
• Load the 2 tables as connection only and leave the query name as being the table name.
• Go into Home > Advanced Editor and replace the code for these 2 queries with the code below under 1 & 2
• Create a blank query and copy in the code under item 3 below into that and close and load that query to Excel.
Each month just copy in the new data to the 2 tables and refresh the output query.

1) I called the Version1 table tblVersion1 and used this as the query name as well
Connection ONLY
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="tblVersion1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Version", each "Version 1")
in
    #"Added Custom"

2) I called the Version2 table tblVersion2 and used this as the query name as well
Connection ONLY
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="tblVersion2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Version", each "Version 2")
in
    #"Added Custom"

3) Then an Append Query that uses the above names tblVersion1 & 2
Close and Load to Excel

Power Query:
let
    Source = Table.Combine({tblVersion1, tblVersion2}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Balance sheet", "Version"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Version]), "Version", "Value", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Version 1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Version 2"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value1", "Subtraction", each [Version 2] - [Version 1], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"Version 1", "Version 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Subtraction", "Difference"}}),
    #"Pivoted Column1" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute]), "Attribute", "Difference", List.Sum)
in
    #"Pivoted Column1"
 

kevdragon1

New Member
Joined
Mar 8, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
You can combine the queries to make it into a single query but it would most likely mean you have to modify the actual M Code.

I took a slightly different approach using Append rather than Merge.
For the below to work unmodified you would need to make sure your headings are the same in both Versions

To use the below
• Give the tables the same name as I have.
• Load the 2 tables as connection only and leave the query name as being the table name.
• Go into Home > Advanced Editor and replace the code for these 2 queries with the code below under 1 & 2
• Create a blank query and copy in the code under item 3 below into that and close and load that query to Excel.
Each month just copy in the new data to the 2 tables and refresh the output query.

1) I called the Version1 table tblVersion1 and used this as the query name as well
Connection ONLY
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="tblVersion1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Version", each "Version 1")
in
    #"Added Custom"

2) I called the Version2 table tblVersion2 and used this as the query name as well
Connection ONLY
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="tblVersion2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Version", each "Version 2")
in
    #"Added Custom"

3) Then an Append Query that uses the above names tblVersion1 & 2
Close and Load to Excel

Power Query:
let
    Source = Table.Combine({tblVersion1, tblVersion2}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Balance sheet", "Version"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Version]), "Version", "Value", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Version 1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Version 2"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value1", "Subtraction", each [Version 2] - [Version 1], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"Version 1", "Version 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Subtraction", "Difference"}}),
    #"Pivoted Column1" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute]), "Attribute", "Difference", List.Sum)
in
    #"Pivoted Column1"
Hi,

Thank you for this.

Wondering what this code does as I was able to recreate this myself but these 2 lines I am not sure I understand. Thank you so much for your help :)

Power Query:
 #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Version 1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Version 2"}),
 

Forum statistics

Threads
1,148,259
Messages
5,745,719
Members
423,969
Latest member
seanguerrero

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
Top