Comparing Two Pivots

**john_london** · 09-21-2010, 11:37 AM

Right then, allow me to explain:

I need to compare two pivot table (lists) of people, to find out which people are not in each Pivot. The reason why I am using Pivots is so the data can be refreshed each month and hopefully save me loads of annoying manual work, hopefully without much fuss.

For example

PIVOT A
-----------
Dawkins,Carl
Harrison,John
Smith,Helen

PIVOT B
----------
Harrison,John
Smith,Helen
Whitfield,Barry

1. I download an excel spreadsheet full of data and then use a Pivot to get a unique list of names from that spreadsheet - PIVOT A
2. I have an existing list of names which I have also put into a Pivot table - PIVOT B
3. I want to have a Pivot (PIVOT C), which gives me people that are in PIVOT B but not Pivot A
4. I want to have a Pivot (PIVOT D), which gives me people that are in PIVOT A, but not PIVOT B

So looking at the data above, PIVOT C would give a list, with one name (Whitfield,Barry)as it is a new name, and PIVOT D would give a list, with one name (Dawkins,Carl), as it is no longer in Pivot B

I hope this explains it?

**Cheeky Charlie** · 09-21-2010, 01:47 PM

I am ok with the idea of using pivots to create a unique list quickly, although the unique list filter is also quite good at it (clue in the name?) a more robust way would be creating a data connection and setting the query only to extract unique records - much neater.

I am a little unsure as to why you would want pivots to create your 'in this list, not in that list' reports.

The way I would approach this would be:
=countif(otherlist,thisthing) copied down the length of each list of names.

Perhaps if you upload a couple of dummy worksheets representing each of your raw sources I can offer some more constructive help.

**john_london** · 09-21-2010, 03:03 PM

Thanks for the prompt reply. It's quite a simple sheet but getting more and more complex as I think about it. I'll try and explain it better:

1. I have an attendance list, a list of people perform a certain role, who must attend a meeting every month.

2. I download a new report every month, essentially an updated list of people, the day before the meeting, to make sure I have an up-to-date-list of people. I copy this report into a worksheet, and have a pivot table which filters out the people as one person may be in multiple rows of the report.

3. If the person is not in the new report, I need to change their status in the attendance list to 'terminated', but still keep them in the attendance list.

4. If there are people in the new report that are not in the attendance list, I need to add them to the attendance list, and set their status to current.

So:
- the first pivot table is a list of the people in the attendance list with 'current' status.
- the second pivot table is a list of the people in the report.

The pivot table I need would look at the first pivot table, compare this list to the second pivot table, and list those people who are not the second pivot table - a list of people I need to change to 'terminated' in the original list.

The second pivot table would look at people in the second pivot table who are not in the first pivot table, therefore listing the people that needed to be added to the attendance list as current.

I've attached my spreadsheet, please take a look! I hope I've been clear!

**john_london** · 09-23-2010, 03:44 PM

actually, I've taken your initial advice and am not using the pivots any more...thanks!

**Cheeky Charlie** · 09-25-2010, 01:37 PM

Cool, re: your defined named ranges for your pivots - try:
Attendance:
=OFFSET(Attendance!$A$5,0,0,COUNTA(Attendance!$A:$A)-2,10)
Roles:
=OFFSET('Roles Report'!$A$1,0,0,COUNTA('Roles Report'!$A:$A),12)

These automatically resize when you paste new data in so you don't have to resize them yourself.

**john_london** · 09-28-2010, 03:56 PM

Hey - thanks!

So I've completely changed this now, I'm going to use column references rather than ranges...

I have a new problem now (of course), but will put it in a different topic.

John