Duplicates Formula
 

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!!

Duplicates Formula
 

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" wrote in message
...
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!!

Duplicates Formula
 

That formula highlighted every job!! : )

"Bob Phillips" wrote:

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" wrote in message
...
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!!

Duplicates Formula
 

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)



"Ryan''s Girl" wrote in message
...
That formula highlighted every job!! : )

"Bob Phillips" wrote:

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" wrote in message
...
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!!