Comparing excel data
Posted on 2014-10-10
I have an excel spreadsheet. Worksheets 1, 2 and 3 all contain data for Full name and email address in 2 separate columns.
Worksheet 1 contains all the data that is in Worksheets 2 and 3, as well as extra records that's not in either of those 2 other Worksheets.
Worksheet 2 contains a copy of some of the records (full rows) from Worksheet 1 (so this data is in both Worksheet 1 and 2 but not in Worksheet 3).
Worksheet 3 also contains a copy of some of the records from Worksheet 1 (so this data is in both Worksheets 1 and 3 but not in Worksheet 2).
In Worksheet 1, I need to find all those records that are in Worksheets 2 and 3 and somehow flag them. The end result would be that I could see all the unique records that are in Worksheet 1 only but not in Worksheets 2 and 3.
I thought about using something like vLookup with a nested if statement and a unique key. This is how I thought it could work:
1) Each of the 3 worksheets currently have data in columns A and B. In column C, I thought that I could concatenate cols A and B to create a unique key which would identify the record.
2) Then in column D of Worksheet 1, I could enter a vLookup function (together with a nested if statement), that would check column C in Worksheets 2 and 3 and compare them with each of the records in Column C in Worksheet 1. If it finds a match, then it could enter "Sheet 2" or "Sheet 3" in that column D which would tell me that those records are actually in those sheets. Then I could filter Worksheet 1 so that I can see the unique records.
As I don't really know how vLookup works, I'm not sure if this is the right way to go but if anyone thinks this is a possible solution, could I have some detailed instructions on how to get this to work?
Appreciate any help.