Determining if duplicate values exist across a row (then do that over 2000 rows)

[TDSexcel]

Hi everyone,

I am trying to determine if the value in cell Y2 also exists anywhere in cells AE2:QE2.

My initial stab at it is

Please Login or Register  to view this content.

This results in a #VALUE! error (I am pretty sure that's because excel doesn't like the AE2:QE2 part).

I would do a formula-driven conditional format for this, but I have 2000+ rows that need to be acted on, so that's not feasible.

Is there any way to have excel check if the Y2 value exists in AE2...QE2, cell by cell?

[etaf]

try
=IF(countif(AE2:QE2, Y2)>0, "Duplicate", "No Match")

[Tony Valko]

Here's another one...

=IF(COUNT(MATCH(Y2,AE2:QE2,0)),"Duplicate","No Match")

[etaf]

@Tony Valko
is there an advantage in processing or other to using that count/match ?
just learning here too

[Tony Valko]

The OP said they need to copy the formulas down about 2000 rows.

In that case the COUNT/MATCH formula is "significantly" faster to calculate relative to the task at hand.

Here are the calculation times of the 2 formulas entered in 2000 rows.

Data Range

	A	B	C	D	E	F	G
1		Test1	Test2	Test3	Test4	Test5	Average
2	COUNTIF	0.06798	0.06799	0.07200	0.06795	0.07218	0.06962
3	MATCH	0.01604	0.01288	0.01256	0.01270	0.01287	0.01341

You may get different results based on the configuration of your machine.

I used the calculation timer code found here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

[etaf]

@Tony Valko
Thanks for taking the time to show that - useful to know

[Tony Valko]

You're welcome!