formula to compare multiple columns

[flashbbeb]

Hi all,

I'm trying to compare two different sets of data on the same sheet (as below), and want to check for uniqueness by a 3 column key. I'm not comparing cell-to-cell, but want to check the entirety of a column so that if a true duplicate appears anywhere between the two sets, a "match" string appears in another column. I'm trying to achieve the example below:

A B C D E F G
234 W 200 234 W 200 match
494 A 100 494 A 100 match
385 W 200 385 T 200 no match
348 T 100 348 A 200 no match

Thanks,
EB

 

[SkipVought]

hi,

Enter as an ARRAY (ctr+SHIFT+ENTER)
[tt]
=IF(ISNA(MATCH(D1&E1&F1,A:A&B:B&C:C,0)),"No Match","Match")
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[flashbbeb]

Thanks, Skip. This unfortunately isn't working...

I would think it would work - concatenating the cells, and doing an exact match against all of cols A, B, and C concatenated. And I did make sure it was in as an array, confirmed the brackets were there after ctrl+shft+enter, but everything listed as "Match".

 

[PHV]

In the G1 cell enter this formula:
=IF(A1&B1&C1=D1&E1&F1,"Match","No match")
and then copy down

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[flashbbeb]

PHV - Thanks for the suggestion.

The problem with that is that I'm not comparing row to row due to the data I have. It would work well if that were the case, but I'm comparing a row of exact cells (so, yes A1&B1&C1) against 3 columns, searching for the same 3 values anywhere within the column.

 

[PHV]

Sorry, but with your posted sample data I get the expected result.
So, please: WHAT DO YOU WANT TO DO ?

 

[SkipVought]

Your statements have not been not consistent.

Please decide EXACTLY what you need and convey your requiresment clearly, concisely and completely.

As PHV stated, this approch works as requested in your OP.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]