Formula that compares two sets of data

CatsIgnoreMe

New Member
Joined
Feb 8, 2016
Messages
15
Forgive me for a basic question, but I'm having trouble wrapping my mind around the best way to this. I have 5 columns of data from our existing records: School | Position | Name | Email | Phone and the same 5 columns of data from an updated source. I want Excel to compare to two sets of data. If there is a match, then a value of "Keep" (or any other value to represent "Keep"). If no match is found, the formula would return "New". Thank you for your help!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Can you post a SMALL sample of your data - say half a dozen records from each data source ?
Make the data up if you don't want to post real data.
Be sure to include some examples that you consider to be a match, and some which are similar but not considered to be a match.
 
Upvote 0
I went to generatedata.com to create these sample records. The 01 set represents existing records in our database (from last year). The 02 set represents new data for this year. The unique data shared between the two sets will be the email address. In the 2nd sample set, three of the records are the same (compared to the first set). Two records are different.

01 Data Set
[TABLE="width: 717"]
<tbody>[TR]
[TD]Company[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]Donec Tempus Ltd[/TD]
[TD]Daquan[/TD]
[TD]Collins[/TD]
[TD]Vestibulum@lobortisquispede.net[/TD]
[/TR]
[TR]
[TD]Ultricies Dignissim Consulting[/TD]
[TD]Hall[/TD]
[TD]Munoz[/TD]
[TD]sociis.natoque@Fuscealiquetmagna.net[/TD]
[/TR]
[TR]
[TD]Mauris Inc.[/TD]
[TD]Jeanette[/TD]
[TD]Jackson[/TD]
[TD]Nullam.feugiat.placerat@eu.co.uk[/TD]
[/TR]
[TR]
[TD]Ut Ltd[/TD]
[TD]Zeus[/TD]
[TD]Wooten[/TD]
[TD]at@velpede.net[/TD]
[/TR]
[TR]
[TD]Metus Vitae Velit Corp.[/TD]
[TD]Desiree[/TD]
[TD]Knowles[/TD]
[TD]lobortis.Class@at.edu
[/TD]
[/TR]
</tbody>[/TABLE]


02 Data Set

[TABLE="class: grid, width: 320"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Company[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Email[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Donec Tempus Ltd[/TD]
[TD]Daquan[/TD]
[TD]Collins[/TD]
[TD="colspan: 2"]Vestibulum@lobortisquispede.net[/TD]
[/TR]
[TR]
[TD]Ultricies Dignissim Consulting[/TD]
[TD]Hall[/TD]
[TD]Munoz[/TD]
[TD="colspan: 2"]sociis.natoque@Fuscealiquetmagna.net[/TD]
[/TR]
[TR]
[TD]Mauris Inc.[/TD]
[TD]Jeanette[/TD]
[TD]Jackson[/TD]
[TD="colspan: 2"]Nullam.feugiat.placerat@eu.co.uk[/TD]
[/TR]
[TR]
[TD]Aliquam Adipiscing Lobortis Corporation[/TD]
[TD]Rajah[/TD]
[TD]Parker[/TD]
[TD="colspan: 2"]et.magnis.dis@nibhlacinia.com[/TD]
[/TR]
[TR]
[TD]Maecenas Company[/TD]
[TD]Geraldine[/TD]
[TD]Hines[/TD]
[TD="colspan: 2"]In.mi@vulputate.com[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
OK, so are you ONLY comparing the email field, and ignoring any matches or mismatches between other fields ?
 
Upvote 0
OK. There are several ways to do this, here's one.

Let's make some assumptions about where your data is located.

Let's say data set 01 is in the range A1:D100, and data set 02 is in the range E1:H100.

To test whether an email address from data set 02 in cell H2 is found in the data set 01 in the range D1:D100, use something like this
=IF(ISERROR(MATCH(H2,D$1:D$100,0)),"NEW","KEEP")

You can then copy this down as far as required.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top