Data Comparison - Multi criteria

[rickgarcia76]

Hello,

I am new here and really enjoy seeing how helpful everyone has been. That said, I need a little help myself.

I have a workbook with 4 worksheets. I need to compare data in the 1st worksheet to data in the other three worksheets and find the matches.

All worksheets have the following columns: Full Name, Address, Phone #.

A match is defined as having the same full name AND either the same address OR phone #. It is not a match if only one criteria is met.

Can someone please help me? Will INDEX/MATCH handle this?


Thanks,
Rick

[Tony Valko]

Post a SMALL sample file and show us what results you expect.

20 rows and a few columns worth of data is plenty. The more "junk" you put in the file the harder it makes it to "analyze" (sift through) the data to solve your problem.

[rickgarcia76]

Thanks Tony. A sample file can be found here: https://drive.google.com/open?id=0B4...FZxeXpMdXNKb0E

[Tony Valko]

OK, I downloaded the file but I'm not sure what to do with it!

[rickgarcia76]

Well, I need to figure out a way to find the 'matches' between the two worksheets. But a 'match' is when full name AND (address OR phone #) are the same. Does that make sense?

[Tony Valko]

Will one list be longer/shorter than the other?

Typically, you'd want to compare the shorter list to the longer list. This takes fewer formulas.

Which phone number needs to match? Most entries have 3 phone numbers. The numbers on Sheet2 have dashes but the numbers on Sheet1 don't have dashes.

To be perfectly honest, this looks like a nightmare!

[rickgarcia76]

Will one list be longer/shorter than the other?

Typically, you'd want to compare the shorter list to the longer list. This takes fewer formulas.

Which phone number needs to match? Most entries have 3 phone numbers. The numbers on Sheet2 have dashes but the numbers on Sheet1 don't have dashes.

To be perfectly honest, this looks like a nightmare!

Yes, one list will almost always be longer than the other. I will be using the home/main number only. And I can massage the phone number data to have them formatted identically.

I was hoping this wasn't that difficult, but maybe that was wishful.

[rickgarcia76]

Tony,

Can you think of an easier way that would achieve my goal? Maybe break this up into smaller parts, then concat result sets at the end, maybe?

[Tony Valko]

In the sample file there are no matches so it's kind of difficult to come up with a good suggestion.

It would be better to make all the phone numbers all the same format using the dashes.

Can you post an updated file that includes a few matches and tell/show me which entries are the matches?