locating duplicates

  • Thread starter Thread starter bshorey
  • Start date Start date
B

bshorey

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.
 
=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
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!
 
How about this in the format|Conditional formatting dialog:

=AND(E1<>"",SUMPRODUCT(--($E$1:$E$99=E1))>1)
(with E1 the activecell)
 
EXACTLY. Thank you!

Dave Peterson said:
How about this in the format|Conditional formatting dialog:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top