locating duplicates
 

using Excel 2003

I have a list of invoice numbers and need to find duplicates. I tried using
conditional formatting in column E as such:

condition 1
formula is =COUNTIF(E:E,E1)1
(cell shading to yellow)

The problem I'm running into is that, for instance, it sees invoice numbers
0000003730 and 03730 as duplicates. I only want it to shade if it's an exact
match, i.e. 03730 and 03730.

I tried using a helper column (F) and in F2 put the formula
=IF(E2=E1,"duplicate","")

But I want ALL cells containing the same number to say "duplicate", i.e. all
occurences of 03730 should state duplicate, not just the second (or third or
fourth!) time it appears.

locating duplicates
 

=countif() does text comparisons.

So '1 and 1 will be counted.

You could use =sumproduct()

=sumproduct(--($e$1:$E$99=e1))

to distinguish between text and numbers.

But if your values are really numbers just with different numberformats, you'll
have to do something different.

About the =sumproduct() formula:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

bshorey wrote:

using Excel 2003

I have a list of invoice numbers and need to find duplicates. I tried using
conditional formatting in column E as such:

condition 1
formula is =COUNTIF(E:E,E1)1
(cell shading to yellow)

The problem I'm running into is that, for instance, it sees invoice numbers
0000003730 and 03730 as duplicates. I only want it to shade if it's an exact
match, i.e. 03730 and 03730.

I tried using a helper column (F) and in F2 put the formula
=IF(E2=E1,"duplicate","")

But I want ALL cells containing the same number to say "duplicate", i.e. all
occurences of 03730 should state duplicate, not just the second (or third or
fourth!) time it appears.

--

Dave Peterson

locating duplicates
 

Thanks, Dave.

I wasn't totally clear on your instructions, so I tried using the sumproduct
in conditional formatting and everything shaded yellow. Instead, I tried
pasting the sumproduct in a helper column (F). It returned numbers that
indicated how many times that particular invoice number occurred in the
spreadsheet (i.e. 2, 5, 16). So essentially it worked.

But it also involved copying and pasting special values into another helper
column (G) to get rid of the formulas, then deleting the original helper
column (F), then doing a bunch of sorts to see what kind of results I had
gotten.

I would prefer color shading to the number return, so if anyone has any
other solutions, I'd like to know!

"Dave Peterson" wrote:

=countif() does text comparisons.

So '1 and 1 will be counted.

You could use =sumproduct()

=sumproduct(--($e$1:$E$99=e1))

to distinguish between text and numbers.

But if your values are really numbers just with different numberformats, you'll
have to do something different.

About the =sumproduct() formula:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

--

Dave Peterson

locating duplicates
 

How about this in the format|Conditional formatting dialog:

=AND(E1<"",SUMPRODUCT(--($E$1:$E$99=E1))1)
(with E1 the activecell)



bshorey wrote:

Thanks, Dave.

I wasn't totally clear on your instructions, so I tried using the sumproduct
in conditional formatting and everything shaded yellow. Instead, I tried
pasting the sumproduct in a helper column (F). It returned numbers that
indicated how many times that particular invoice number occurred in the
spreadsheet (i.e. 2, 5, 16). So essentially it worked.

But it also involved copying and pasting special values into another helper
column (G) to get rid of the formulas, then deleting the original helper
column (F), then doing a bunch of sorts to see what kind of results I had
gotten.

I would prefer color shading to the number return, so if anyone has any
other solutions, I'd like to know!

"Dave Peterson" wrote:

=countif() does text comparisons.

So '1 and 1 will be counted.

You could use =sumproduct()

=sumproduct(--($e$1:$E$99=e1))

to distinguish between text and numbers.

But if your values are really numbers just with different numberformats, you'll
have to do something different.

About the =sumproduct() formula:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

--

Dave Peterson


--

Dave Peterson

locating duplicates
 

EXACTLY. Thank you!

"Dave Peterson" wrote:

How about this in the format|Conditional formatting dialog:

=AND(E1<"",SUMPRODUCT(--($E$1:$E$99=E1))1)
(with E1 the activecell)



bshorey wrote:

Thanks, Dave.

I wasn't totally clear on your instructions, so I tried using the sumproduct
in conditional formatting and everything shaded yellow. Instead, I tried
pasting the sumproduct in a helper column (F). It returned numbers that
indicated how many times that particular invoice number occurred in the
spreadsheet (i.e. 2, 5, 16). So essentially it worked.

But it also involved copying and pasting special values into another helper
column (G) to get rid of the formulas, then deleting the original helper
column (F), then doing a bunch of sorts to see what kind of results I had
gotten.

I would prefer color shading to the number return, so if anyone has any
other solutions, I'd like to know!

"Dave Peterson" wrote:

=countif() does text comparisons.

So '1 and 1 will be counted.

You could use =sumproduct()

=sumproduct(--($e$1:$E$99=e1))

to distinguish between text and numbers.

But if your values are really numbers just with different numberformats, you'll
have to do something different.

About the =sumproduct() formula:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

--

Dave Peterson


--

Dave Peterson