How To Compare Multiple Lists of Names with a Pivot Table - Excel Campus
17

How To Compare Multiple Lists of Names with a Pivot Table

In this post I will explain how to compare two or more lists of names using a Pivot Table.

How To Compare Lists of Names with Pivot Table

In this example we have lists of names for people that signed up to volunteer for a beach cleanup event.  The event has been running for three years and we have one list for each of the last three years.

We want to compare these lists and answer some of the following questions about our volunteers.

  • Who has signed up all three years?
  • Who signed up last year, but did not sign up this year?
  • Who is new to the event this year?

Video

The Pivot Table List Comparison Technique

We can answer all of these questions with one very simple Pivot Table.  This technique is very easy to implement and does not require any formulas.

It should also help you understand how Pivot Tables work to consolidate and summarize data.

In three simple steps we are going to create the pivot table and answer our questions.

You can download the sample file I’m using to follow along.

Pivot Table List Comparison Technique.xlsx (27.6 KB)

Don’t forget to sign-up for my free email newsletter to get more tips and downloads like this.

Step #1 – Combine the Lists

The first step is to prepare our lists.  In the file we have three worksheets that contain a list of names for volunteers that signed up each year.

We need to combine these three lists into one list that contains all the data for all three years.

Combine Lists for Pivot Table Name Comparison

The following are the steps for combining the lists.

  1. Make a copy of the ‘2012’ sheet and rename it to ‘Combined Data’.
  2. In cell E1 add the text “Year”.  This new column will represent the year for each list of data.
  3. Enter “2012” in cell B2 and copy it down to the end of the list.
  4. Copy the 2013 data to the bottom of the list on the ‘Combined Data’ sheet.
  5. Enter “2013” in column B in the first row of the 2013 data (cell B12), and copy it down to the end of the list.
  6. Repeat steps 4 & 5 for the 2014 data.

You should now have a long list of data that contains all the data rows for all three years, with a new column (B) that specifies which year the data row belongs to.

Please see my article on structuring source data for pivot tables for detailed explanation on why the data is organized like this.

Step #2 – Create the Pivot Table

We can now create a Pivot Table based on our ‘Combined Data’ list to start making comparisons.

Create Pivot Table Name Comparison

Here are the steps to creating the Pivot Table.

  1. Select a cell in the Combined List and press the Pivot Table button on the Insert tab of the Ribbon.  Press OK on the prompt window to create a Pivot Table on a new worksheet.
  2. Add the Name field to the Rows area of the Pivot Table.
  3. Add the Year field to the Columns area of the Pivot Table.
  4. Add the Name field to the Values area of the Pivot Table.  This will create a Count of Names measure.

You should now have a list of names of all the volunteers that signed up over the last three years.  The “1’s” in columns B:D tell us that the person was a volunteer in that year.

The Pivot Table Explained

When we add the Name field to the Rows area of the pivot table (step 2 above), Excel automatically consolidates the list for us and creates a row for each unique name from the combined list.  This means that names that appear in multiple years will only be listed once.

For example, the name Asher Mays appears three times in the combined list (source data of the pivot table).  But the pivot table only displays the name once in row 6 (image below).  This is a consolidated list of unique values.

The Pivot Table Explained - Compare Lists

The same thing happens when we add the Years field to the Columns area (step 3 above).  The years 2012, 2013, and 2014 are listed many times in column B of the combined data list.  But each of the years only appears once in columns B:D of the pivot table.

When we add the Names field to the Values area (step 4 above), Excel calculates how many times the name and year combination appear for each intersection in the pivot table.  For example, cell C6 of the pivot table is the intersection of the name Asher Mays and the year 2013.  This combination appears once in the data source, so a “1” is displayed in cell C6.

If a cell in the pivot table is blank then that name and year combination does not exist in the source data.  That means the person did not volunteer that year.

Step #3 – Answer Our Questions (Analyze the Results)

We can now use this one Pivot Table to answer all of our questions.

Question #1 – Who has signed up all three years?

Column E of the Pivot Table contains the Grand Total (sum of columns B:D).   People that volunteered all three years will have a “3” in column E.

We should sort the pivot table so all the people with a “3” in column E appear at the top of the list.  This will make it easier to find the names.

Sort Pivot Table Name Comparison

These people will have a “1” in each column from B:D and the sum of these 1’s = 3.

Names That Appeared in All 3 Lists Pivot Table Name Comparison

If your list is longer, you can copy the Pivot Table data to another sheet and use Excel’s Filter features to sort and filter the data further.  You can also use an Excel Table to automatically summarize your filtered results.  Checkout my video on Excel Tables to learn about tables.

Question #2 – Who signed up last year, but did not sign up this year?

To answer this question we need to know who had a “1” in the 2013 column and a “blank” in the 2014 column.

The filter features of the Pivot Table can be somewhat limited for this exercise, so I will copy and paste the pivot table data to another sheet and apply Filters to do the analysis.

Volunteer This Year NOT Last Year Pivot Table Name Comparison

These are people we might want to contact to see if they can make it this year.

Question #3 – Who is new to the event this year?

To answer this question we just need to know who has a “1” in the 2014 column, and blanks for 2012 and 2013.

Who Is New This Year Table Name Comparison

This tells us that the person has never volunteered before, and we want to be extra nice to them so they come back next year. 🙂

Checkout my article on 7 Keyboard Shortcuts for the Drop-down Filters for some quick tips on using the Filters.

What’s Next?

This technique to compare multiple lists of data using a Pivot Table is “must-know” for any analyst.  It is very easy once you get the hang of it, and it will save you a lot of time.

The alternative is to use a lot of VLOOKUP, SUMIF, or COUNTIF formulas, and you will spend a lot of time manually creating a report.

The Pivot Table technique can also let you quickly pivot to answer other questions about your data.  For example, if we wanted to know what city our volunteers are coming from, we could easily add the City field to the Rows area of the Pivot Table.

This allows us to further analyze our data  and see that we might be continually attracting volunteers from some cities and not others.

City-Name Pivot Table Name Comparison

I’ve really just scratched the surface of how you can use this technique.

In an upcoming post I will show you how you can use this same technique to reconcile transaction data.  Do you have a list of accounts or customer (CRM) data, where you want to see what changed from last month to this month?

I will show you how to use this technique to create some very powerful reports that will give you a lot of insight into your data.

Please subscribe below to get an email when this new post is available.  And leave a comment below with any questions. 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 17 comments
chris - September 6, 2017

Joe,
Thanks for the pivot post. How would I structure data in a spread sheet where I can represent w/in a pivot table for the following fields: type of work, date (week and year), name. I would also be interested in weighting the work (difficulty, stress) in addition to just quantifying weeks worked. Thanks in advance for any help you can offer. v/r, chris

Reply
Garyn - April 9, 2017

I need to know how to do this in reverse. I already have a pivot table sheet, but needing to break the data down into individual sheets, to look just as how your individual lists looked to start out with.

Reply
    Jon Acampora - April 10, 2017

    Hi Garyn,
    You could use the Show Report Filter Pages feature for this. I don’t have a tutorial, but you would drag the Year field into the Filters area, then run the Show Report Filter Pages feature. It’s on the Option/Analyze tab in the Options drop-down menu. I will write a post on this in the future.

    Reply
Nadine Patterson - March 22, 2017

Thank you for the quick refresher.

Reply
Trish Noakes - December 8, 2016

Our membership database stores names in 2 fields first name and surname – so when I run a pivot table I have to put both into rows – but then very annoyingly Excel recognises the same surname and groups 2 different people (albeit with the same surname) together – how do i avoid this?

Reply
    Jon Acampora - December 10, 2016

    Hi Trish,
    Great question! There are a few ways to go about it.

    1. You can change the pivot table layout to Tabular format and Repeat the Labels. This is done from the Design tab in the ribbon with a cell in the pivot table selected. Here is a screenshot.

    Pivot Table Compact Form Layout Repeat Item Labels

    2. Another option is to concatenate/join the First Name and Last Name in a new column called Full Name. Then add this name to the pivot table. This can be done with a simple formula.

    =A2 &” “& B2

    Assuming A2 contains the first name and B2 contains the last name.

    If this is something you do often, my PivotPal add-in has a feature called My Pivot Layouts. This feature allows you to create custom profiles for your pivot table layouts and apply them with a click of a button. So, if you find yourself applying the compact format and repeating labels (solution #1) often, then this feature will save you some time.

    I hope that helps.

    Reply
karolina - September 7, 2015

Great Article! But where is the link to see the next video? The video about “how to use this technique to create some very powerful reports that will give you a lot of insight into your data”?

Reply
Hesham - January 27, 2015

This video was amazing and works very well with me. I have three big lists to compare (57973 items) and i followed what exactly mentioned here, however, if I want to get the total numbers, for example how many volunteers participated only in the first year, or how many volunteers participated in the three years.

Reply
    Jon Acampora - January 29, 2015

    Thank you Hesham! The total numbers will be displayed in the Total Row at the bottom of the pivot table. If you want to sum all three columns then you can add a Total Column.

    To turn the Total Row or Columns on/off:
    1. Select any cell in the pivot table.
    2. Go to the Design tab on the ribbon.
    3. Click the Grand Totals button on the left side of the ribbon.
    4. Select the option that says “On for Rows and Columns”.

    That will turn the totals on and you will see the Grand Total of all volunteers for the three years in the bottom right corner of the pivot table.

    Please let me know if you have any questions. Thanks again!

    Reply
Eileen Morrison - January 23, 2015

Thanks so much for this short video. I look forward to your webinar.

Reply
    Jon Acampora - January 24, 2015

    Thanks Eileen! I am glad you enjoyed it.

    Are you referring to the PivotTable Webinar by John Michaloudis? If so that’s a different John, but the webinar is great and you will learn a lot of awesome pivot table techniques. You can click the link above to register for the webinar if you haven’t done so already. Thanks again!

    Reply
Pedro J. Davila - July 10, 2014

Jon:

Thanks for providing these Excel Tutorials with such excellent presentation. I have a suggestion for your How to Compare Lists of Names with a Pivot Table to reduce the effort of cutting and pasting using RDBmerge, see link: http://www.rondebruin.nl/mac/addins/mergemac.htm for Macs, or http://www.rondebruin.nl/win/addins/rdbmerge.htm for windows. RDBMerge is a user friendly way to Merge Data from Multiple Excel Workbooks, csv and xml files into a Summary Workbook. It has been used at my work to combine several worksheets into one summary sheet, which in turn is fed into a pivot table.

Your tips on how to “fill down” formulas and values in the video was very helpful. I usually usually convert my data into Tables, in that way I get a formatted layout and fill down occurs automatically when I add a formula to any empty column, not so when it is a value. Lamentably, Tables don’t work as data source for pivot tables.

Have a great day, and keep up your good work!

Reply
    Jon Acampora - July 14, 2014

    Hi Pedro,

    Thank you for your comment and suggestion! The RDBMerge tool would be very useful if this is a process you are doing frequently.

    I find myself using this technique all the time, as it is a great alternative to using lookup formulas.

    I also use Tables and the autofill feature is definitely a time saver. Here is a video tutorial I did on Tables for anyone that wants to learn more about this great tool in Excel.

    Thanks again Pedro!

    Reply
John - July 8, 2014

A real life case solved by a Pivot Table! Their power is almost unlimited.

You could also use Conditional Formatting to highlight certain values as well to make them stand out, you can even highlight blank cells.

Great post!

Reply
    Jon Acampora - July 8, 2014

    Another great idea! Thanks John! The conditional formatting could be very useful if you wanted to visually identify and display trends with these types of comparisons.

    Reply
Jeff Weir - July 8, 2014

Hi Jon. Great treatment of the subject. Couple of comments:
If your list is longer, you can copy the Pivot Table data to another sheet and use Excel’s Filter features to sort and filter the data further. Or you could apply the autofilter to the PivotTable as per Mike’s trick at http://datapigtechnologies.com/blog/index.php/autofilter-a-pivottable/

To answer this question we need to know who had a “1″ in the 2013 column and a “blank” in the 2014 column.
Again, you can use Mike’s Autofilter trick.

You’ve just got to remember to clear the filter when you’re done, otherwise the pivot behind the scenes moves around and falls into the ‘cracks’ of the hidden rows. Hard to explain.

Reply

Leave a Reply:

FREE Live Webinar 

The 5 Secrets to Understanding Pivot Tables
x

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x