Data Comparison take four, using a PivotTable
This is a series of articles with solutions that evolve as Excel evolves.
- Data Comparison take one, AND or EXACT
- Data Comparison take two, EQUALS
- Data Comparison take three, EQUALS and HYPERLINK
- Data Comparison take four, PivotTable
- Data Comparison take five, Power Query
Excel 365
Do you have two sets of data, with a common column, where you want to compare amounts and find remaining balances? Essentially reconciling two sets of data.
Here is way to do that using a PivotTable.
Overview
With two sets of data linked by a common value
Copy the data into a single source, line up the columns and add a source column.
One data source must have the sign convention for amounts converted to a negative value.
Pivot the data using the common value.
Convert the PivotTable to values and remove any zero values.
The remaining amounts are the “unreconciled” amounts
Consolidate the data
First copy your original data into a blank worksheet.
Copy the data to compare into the same worksheet, under to the original data.
Line up the columns. If there are columns that don’t match, keep them in the data but some of the column cells will have no value.
After the last column add a Source column. Give each set of data an identifiable name.
Enter -1 into an empty cell away from your data.
Copy the value.
Select all the amounts for one data source.
Right click on the selection and click on Paste Special and then on the second Paste Special option. In the Operation section click on the Multiply radio button and then click on the OK button.
One data source will have now have the opposite sign convention for all the amounts.
The -1 value that was copied can be deleted.
Click inside the combined data, press Ctrl+T to turn the data into a table.
Make sure that My table has headers is ticked.
Click inside the combined data, from the Table Design menu tab click on Summarize with PivotTable
Choose where to put the PivotTable and click on the OK button.
New Worksheet is usually the best option.
Choose the data to put into the PivotTable.
Put the Source column into the Columns section.
The Amount column goes into the Values section.
Your “Common column” goes into the Rows section.
In the screenshots, the Invoice No. column has been used as the “Common column”.
Other fields can be added to the Rows section but they need to be common between both data sources.
The PivotTable will be populated with data.
Any amounts not equal to zero in the Grand Total column are amounts that do not reconcile.
Conditional formatting can be applied to highlight the Grand Total amounts that are not zero.
To show the breakdown of a Grand Total amount, double click on the Grand Total amount for the row. A new Worksheet will be added with the rows that make up the amount from the combined source data.
To simplify the view of the data, click on the pivot table common column dropdown. Click on Value Filters and Does Not Equal….
A Value Filter window will be displayed, set the filter value to 0 and click on the OK button.
The data will be filtered.
This method works very well, to match or reconcile, large data sets containing multiple data points with the same common values.