Matching a range of values

**creed1101** · 08-18-2011, 11:52 AM

Hi

I've want to be able to find out if a range or values in a specific sequence can be found elsewhere in a table.

e.g.
A B C D
2 2 2 3
1 2 3 4
2 2 2 3

I know how to match two columns but how do you match multiple columns with number in that sequence?

Thanks

Nik

**NBVC** · 08-18-2011, 11:56 AM

Can you give an example of what you are looking for as a result and why?

**creed1101** · 08-18-2011, 12:15 PM

I have a dataset (3000 rows) based on questionnaire responses where the unique identifier has been coded incorrectly. I want to check that there are not any duplicates in this dataset. As we collect some demographic info as well as questionnaire responses I wanted to check that sequence of responses and the demo info doesn't appear in the same order as they maybe duplicates.

I just want a true/false returned for each set of values

**NBVC** · 08-18-2011, 12:27 PM

if there are not alot of columns, I would concatenate the columns, separating each with a special symbol, in order to avoid crossovers

e.g.

=A1&"^^"&B1&"^^"&C1&"^^"&D1

copied down

then you can use Countif to find if there are dupes:

=COUNTIF($E$1:$E1,$E1)>1

copied down (where E1 contains first concatenation formula)

TRUE means there are duplicates.