Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Comparing Two Spreadsheets
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 | +-------------------------------------------------------------------+ -- DCSwearingen ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=468344 |
#2
|
|||
|
|||
Yes - I'm sure there is, but I can't open the attachment.
-- tenaj "DCSwearingen" wrote: 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 | +-------------------------------------------------------------------+ -- DCSwearingen ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=468344 |
#3
|
|||
|
|||
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 | +-------------------------------------------------------------------+ -- DCSwearingen ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=468344 |
#4
|
|||
|
|||
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" wrote: 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 | +-------------------------------------------------------------------+ -- DCSwearingen ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=468344 |
#5
|
|||
|
|||
Comparing Two Spreadsheets
Good Morning, GeorgiaW!! I just read this solution this morning. I think it is going to work beautifully for me. Many Thanks!! -- DCSwearingen ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=468344 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing excel spreadsheets from command line | Excel Discussion (Misc queries) | |||
Comparing data and updating spreadsheets | Excel Discussion (Misc queries) | |||
comparing 2 spreadsheets | Excel Discussion (Misc queries) | |||
Comparing Spreadsheets | Excel Worksheet Functions | |||
Please Help (Comparing Spreadsheets) | Excel Worksheet Functions |