• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

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.
0
gwh2
Asked:
gwh2
  • 3
  • 2
  • 2
1 Solution
 
ProfessorJimJamCommented:
Do you want it with vba or formula?
0
 
gwh2Author Commented:
Thanks for the reply,

I'd rather have a formula if possible?
0
 
ProfessorJimJamCommented:
please find attached workbook that has the solution of what you have requested.
EE.xlsx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob HensonIT & Database AssistantCommented:
Alternative without the need for Array formula:

=IF(NOT(ISERROR(MATCH($B2,Sheet2!$B:$B,0))),"Sheet 2",IF(NOT(ISERROR(MATCH($B2,Sheet3!$B:$B,0))),"Sheet 3","Sheet 1"))

Assumes that the e-mail address is already unique.

Thanks
Rob H
0
 
ProfessorJimJamCommented:
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
0
 
Rob HensonIT & Database AssistantCommented:
I spotted the comment about joining the two fields together but couldn't see any examples in the sample that would require that.
0
 
gwh2Author Commented:
Thanks very much - appreciate it
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now