Duplicates Formula

[Ryan''s Girl]

On my spreadsheet I have a column (B) for Job Numbers. Example of what the
job numbers look like....
1929A 90
07747E L
20299A EA
I have rows and rows of job numbers that I need to make sure they are not
duplicates.
I have a formula in Conditional Formatting to find any duplicate job numbers
that looks like this =COUNTIF(B:B,B2)>1
If excel finds any duplicates it highlights them orange. Sometimes excel
will only hightlight one of the duplicates and I need it to highlight ALL of
the duplicates not just the first one it sees. Sometimes the formula works
where it will highlight both but not always. There have even been times that
I have found duplicates that it didn't catch!! This is not good! Is there
something wrong with my formula? How can I make this formula work correctly?
Thanks so much in advance...You guys always help me find the answers I need
to help me do my job!!

 

[Bob Phillips]

It might be leading, trailing spaces, so try this

=SUMPRODUCT(--(TRIM(B2:B1000)=TRIM(B2))>1

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ryan''s Girl]

That formula highlighted every job!! : )

It might be leading, trailing spaces, so try this

=SUMPRODUCT(--(TRIM(B2:B1000)=TRIM(B2))>1

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

It should have been

=SUMPRODUCT(--(TRIM($B$2:$B$1000)=TRIM(B2)))>1


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)