I have two spreadsheets of information that need to be compared against each other. The first sheet contains the original data. The second sheet contains the same information as found in the first but less certain records that should not belong...my IT guys had originally dumped data into a new system but were pulling data from two sources. My job is two figure out which records were deleted from the second dump. Each record from each spreadsheet has it's own unique key. Unfortunately, I can not use the key for comparisons because every time we rerun the program the key number changes.
The first spreadsheet for example is as follows:
Key Product Color Size City
1 Apple Green Small Atlanta
2 Apple Green Medium Houston
3 Apple Green Medium Las Vegas
4 Apple Green Large Atlanta
5 Apple Green Large Atlanta
6 Apple Red Small New York
7 Apple Red Medium Atlanta
8 Apple Red Medium Madison
9 Apple Red Large Las Vegas
10 Apple Red Large Las Vegas
The second spreadsheet for example is as follows:
Key Product Color Size City
11 Apple Green Small Atlanta
13 Apple Green Medium Las Vegas
14 Apple Green Large Atlanta
16 Apple Red Small New York
17 Apple Red Medium Atlanta
19 Apple Red Large Las Vegas
20 Apple Red Large Las Vegas
I need to put an indicator in the first spreadsheet to indicate if the record was found or removed in the second sheet (i.e. 1=found, 2=not found). For example: record 1=1; record 2=2, record 3=1; record 4=1.....and so on. The problem I may run into however is that records 4/5 and records 9/10 are exactly the same. When I come to record 5, the program is going to find the same information in the second set of data but record 4 has already used this record and I do not want to use it again.....record 5 should = 2 (not found). As for records 9 & 10, both should have indicator = 1, as both were found in second spreadsheet.
The final output should be as follows: record 1=1; record 2=2, record 3=1; record 4=1, record 5=2, record 6=1, record 7=1, record 8=2, record 9=1, record 10=1
Thanks in advance for any comments.
Bookmarks