MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2020: Use a Pivot Table to Compare Lists

May 27, 2020 - by

Excel Use a Pivot Table to Compare Lists. Photo Credit: Element5 Digital at Unsplash.com

When you think of comparing lists, you probably think of VLOOKUP. If you have two lists to compare, you need to add two columns of VLOOKUP. In the figure below, you are trying to compare Tuesday to Monday and Wednesday to Tuesday and maybe even Wednesday to Monday. It is going to take a lot of VLOOKUP columns to figure out who was added to and dropped from each list.

Three lists are shown in Excel. Each list has a name and a number. There are lists from Monday, Tuesday, and Wednesday.

You can use pivot tables to make this job far easier. Combine all of your lists into a single list with a new column called Source. In the Source column, identify which list the data came from.

Combine the three lists into one super list. There is a third column, called Source. The Source column indicates if this record came from Monday, Tuesday, or Wednesday list.

Build a pivot table from the combined list, with Name in rows, RSVP in values, and Source in columns. Turn off the Grand Total row, and you have a neat list showing a superset from day to day, as shown below.

A pivot table with Name along the left column and source across the top. It is easy to see for each name if they were blank on Monday, appeared on Tuesday, or the number changed on Wednesday.

Title Photo: Element5 Digital at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.