Comparing Two Spreadsheets

[DCSwearingen]

Each week a spreadsheet is created by pulling a list of active employees
from the payroll database. Obviously the payroll database is very
restricted for access, so I have to depend on the spreadsheet.

I do some tracking of employee involvement, so I need to have my
listing up to date, but I don't want to lose the ideas from employees
just because they are no longer active employees.

Thus my question. Is there a way to compare the list from last week's
spreadsheet to this week's spreadsheet and flag new listings that
appear on the new sheet, but were not on the old sheet. And conversely
flag the names that were on the old sheet, but not on the new sheet.

I have attached a zip file with two worksheet tabs that I might use.
Sheet1 has approximately 45 fewer names than Sheet2. I want to retain
the names from sheet2, but flag them as inactive.


+-------------------------------------------------------------------+
|Filename: Lists.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3832 |
+-------------------------------------------------------------------+

 

[Tenaj]

Yes - I'm sure there is, but I can't open the attachment.

 

[DCSwearingen]

I tried to attach an Excel document and could not, so I tried to attach
the document as a zipped file and it did attach.

I have now attached two files, one is List1.txt and the other is
List2.txt. They are tab delineated text files and can be opened with
Excel.

I use to get an e-mail whenever there was a posting to a thread I was
subscribed, but I never got one on this.


+-------------------------------------------------------------------+
|Filename: List2.txt |
|Download: http://www.excelforum.com/attachment.php?postid=3872 |
+-------------------------------------------------------------------+

 

[GeorgiaW]

I have been struggling with this same type of scenario on and off for a few
months. I finally have the ultimate solution for dealing with two lists that
have matches and also potentially many differences. Insert a column to the
right of the first set of data and fill it down with the number 1. Insert a
column to the right of the second set of data and fill it down with -1. Copy
both sets of data into a new worksheet and append the sets of data so that
the 1's and -1's all line up with one another. Label the columns, calling
the column that contains the 1's and -1's "Count". Now let the Pivot Table
magic begin. Using the standard Pivot Table wizard to create a Pivot table
in some cells further to the right of where of your sets of data have been
pasted, just specify your entire range of data including the Count column.
Drag and Drop "Names" into the "Drop Row Fields Here" space on the Pivot
Table, then Drag and Drop "Count" into the "Drop Data Items Here" space on
the Pivot Table. What you end up with is 1's next to those that are in the
first list, but not in the second, 0's next to those that are in both lists,
and -1's next to those that are in the second list, but not in the first!
You have now identified all the differences between the two lists quite
easily!

 

[DCSwearingen]

Good Morning, GeorgiaW!!

I just read this solution this morning.

I think it is going to work beautifully for me.

Many Thanks!!