Solved

Comparing excel data

Posted on 2014-10-10
7
86 Views
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.
0
Comment
Question by:gwh2
  • 3
  • 2
  • 2
7 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40374533
Do you want it with vba or formula?
0
 
LVL 1

Author Comment

by:gwh2
ID: 40374668
Thanks for the reply,

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

Accepted Solution

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

 
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.

Thanks
Rob H
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
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
0
 
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.
0
 
LVL 1

Author Closing Comment

by:gwh2
ID: 40387483
Thanks very much - appreciate it
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

808 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