If you've been an analyst for more than a week, you've been asked to compare two separate tables to come up with some brilliant analysis about the differences between them. Frankly, the task of comparing two separate tables in Excel is so common, it's suprising there aren't more/better tools in Excel to handle these types of analyses.
Now, I'm personally a database guy, preferring to use Access or SQL Server to find the differences between two tables. However, I definately think there is value in having a few good tools in Excel to do this kind of comparison.
While we wait for that magic solution from Microsoft, we have our hacky workarounds. Today's post will show you one particularly hacky workaround I use, leveraging a pivot table to compare two tables.
.
.
The Task at Hand
I have two tables here that show customers in 2003 and in 2004. My task is to identify all customers in 2003 that are no longer customers in 2004.
I made the tables in this example small for instructional purposes. Imagine we're working with something bigger here.
Step 1: Consolidate the two tables into one.
The idea is to create one table you can use to pivot. Be sure you have a way to tag which data comes from which table. In this example, I have a column called Year that will serve this purpose.
Step 2: Create your pivot table.
Create the pivot table, formatting it so that the table 'tag' (the identifier telling you which table the data came from) is in the column area of the pivot table. In this example, I've got years in the column area, and customers in the row area. The data area contains the count records for each customer name.
As you can see, you instantly get a visual indication of which customers are only in the 2003 table, which are in the 2004 table, and which are in both tables.
Step 3: Group the desired records.
In this example, since I want to pull out the customers in 2003 that are not present in 2004, I'm grouping those pivot items to get one big number.
Step 4: Double click on the grouped total.
Once I have a grouped total, I can double-click the number to reveal all the customers that make up that total.
It looks like there are six customers that are in the 2003 table and not in the 2004 table.
This is technique is not the cleanest way to compare two tables, but it sure does make it easy in the absense of a proper database.
In step 3, the pivot table that you create is showing the default Count of each record. In the field list, you can instead drag the revenue field into the data portion, change it to Sum, and you would be finished with your analysis. It will then show you the total revenue for each year for each customer.
" … preferring to use Access or SQL Server to find the differences between two tables."
Any chance you have a post or a page on this topic, or is one forthcoming?
JP: I don't have a page on it, but I can definately cover this in another post.
surely consolidate, count?
Using the count in a pivot table is a really nice visual way to see where the records are.
One good reason for doing this at the database end is that if the records in the database change, your report will be up to date with a simple refresh. Whereas a table that comes from a double-clicked pivot field is static.
Hey Mike…do you have any luck getting SQL queries with temp tables to run within an Excel data connection?
I've got one horrendously complicated query that makes major use of temp tables, and it runs okay. But I can't get any other queries with temp tables to work. Even something as simple as:
SELECT membership_id
into #temp
FROM MEMBERSHIP
select membership_id
from #temp
drop table #temp
This thows me an error "the query could not run, or the database table could not be opened."
I've tried heaps of variations, and they all work within SQL Query Analyzer, but not directly in Excel. Yet the complicated query I mention above works fine. I even tried overwriting the complicated query with the simple query above to see if it was the connection string or sometthing, but still had no luck.
Any ideas on this?
Cheers
Jeff
Uhmm… Pivots are one way to do this and yes, a database is another, but there are about a dozed cheap (or free) comparison tools on the market that do a heap more than simply compare 2 tables. I know that these are outside the Excel framework, but any reason why these are being considered?