CAE5942
asked on
Comparing excel data
Hi everyone,
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.
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.
Do you want it with vba or formula?
ASKER
Thanks for the reply,
I'd rather have a formula if possible?
I'd rather have a formula if possible?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Alternative without the need for Array formula:
=IF(NOT(ISERROR(MATCH($B2,
Assumes that the e-mail address is already unique.
Thanks
Rob H
=IF(NOT(ISERROR(MATCH($B2,
Assumes that the e-mail address is already unique.
Thanks
Rob H
It had to be in array formula because the author's request is not to match B column but both column A and B and hence array formula was provided
I spotted the comment about joining the two fields together but couldn't see any examples in the sample that would require that.
ASKER
Thanks very much - appreciate it