Excel is highlighting incorrect duplicate values

Originally Posted by omahaNative_1023

dont see any that are duplicates. col a is combo of all other cols. i sorted on col c, then b, then d. did not see duplicates. what are 2 that are bad?

There are actually plenty of duplicates, try a test in a helper column using =COUNTIF($A$2:A2,A2)

Cant figure out (yet) why it is highlighting non-dups though

Hmmm you are correct, but in the orig file, my formula still shows multiples using countif()...

	A	B	C	D	E	F
1	A (B&C&D)	B	C	D	COUNTIF($A$2:A2,A2)
2	99743228591205916470	9974322859	470	1205916	1
3	99743228591205916480	9974322859	480	1205916	2
4	99743228591205916490	9974322859	490	1205916	3
5	99743228591205916500	9974322859	500	1205916	4
6	99743228591205916510	9974322859	510	1205916	5
7	99743228591205916520	9974322859	520	1205916	6
8	99743228591205916530	9974322859	530	1205916	7
9	99743228591601024460	9974322859	460	1601024	1
10	99743228591601024480	9974322859	480	1601024	2
11	99743228591601024510	9974322859	510	1601024	3
12	99743228591172492510	9974322859	510	1172492	1
13	99743759221205916300	9974375922	300	1205916	1

I need to dig a bit deeper on this.

In fact, it looks even worse when I do it this way...

	A	B	C	D	E	F
1	A (B&C&D)	B	C	D	COUNTIF($A$2:A372,A2)
2	99743228591205916470	9974322859	470	1205916	7
3	99743228591205916480	9974322859	480	1205916	7
4	99743228591205916490	9974322859	490	1205916	7
5	99743228591205916500	9974322859	500	1205916	7
6	99743228591205916510	9974322859	510	1205916	7
7	99743228591205916520	9974322859	520	1205916	7
8	99743228591205916530	9974322859	530	1205916	7
9	99743228591601024460	9974322859	460	1601024	3
10	99743228591601024480	9974322859	480	1601024	3
11	99743228591601024510	9974322859	510	1601024	3
12	99743228591172492510	9974322859	510	1172492	1
13	99743759221205916300	9974375922	300	1205916	1

or use this:

=COUNTIF($A$2:$A$372,"*"&$A2)>1

or

=SUMPRODUCT(--($A$2:$A$372=$A2))>1

Originally Posted by omahaNative_1023

fd,

you should not use COUNTIF($A$2:A372,A2) or COUNTIF($A$2:A2,A2). just give full range. not sure why you do that. this really look weird to me:

$A$2:A2,A2

i just use what is normal and cover everything

COUNTIF(A:A,A2)

While using a full-column range is not incorrect, and is often a good approach to take, sometimes using just a specific range can be a better option (for instance there may be other tables below that you need to exclude, or - in this case - I wanted to make sure I was using just that range of data)

As far as using $A$2:A2,A2 is concerned, that is a useful technique to use if you want to show a progressive count of duplicates - I was trying to see where it was picking up duplicates as the formula was copied down.