Comparing excel data

Posted on 2014-10-10
Last Modified: 2014-10-17
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.
Question by:gwh2
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 26

Expert Comment

ID: 40374533
Do you want it with vba or formula?

Author Comment

ID: 40374668
Thanks for the reply,

I'd rather have a formula if possible?
LVL 26

Accepted Solution

ProfessorJimJam earned 500 total points
ID: 40379465
please find attached workbook that has the solution of what you have requested.
Industry Leaders: 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!

LVL 33

Expert Comment

by:Rob Henson
ID: 40384813
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.

Rob H
LVL 26

Expert Comment

ID: 40384916
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
LVL 33

Expert Comment

by:Rob Henson
ID: 40385195
I spotted the comment about joining the two fields together but couldn't see any examples in the sample that would require that.

Author Closing Comment

ID: 40387483
Thanks very much - appreciate it

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question