how to find duplicate cells in large array of numbers

W

wonkywombat

i have about 5000 numbers(ticket numbers) in block of cells , and i need to
find out if any numbers are duplicated with another in any of the other cells.
how do i go about this?
 
D

DaveB

Assume your ticket numbers are in the range A1:D1000, this formula will
output a single number to tell you how many duplicates there are located
within that range:

=SUMPRODUCT(--(IF(COUNTIF(A1:D1000,A1:D1000)>1,1,0)))

This is an array formula so you will need to use ctrl+shift+enter to enter
it in.
 
D

DaveB

I actually just red your question again and think I mis-understood it, if you
were looking to determine which values are duplicates, and not just how many
total you have in your range, you could use this:

Assume your ticket numbers are in the range A1:A5000. Put this code in B1
and copy down to the end:

=IF(COUNTIF($A$1:$A$5000,A1)>1,"Duplicate","Not Duplicate")

Does that help?
 
W

wonkywombat

Thanks dave, it took me a bit of time to actually understand how it worked so
that i could alter it to suit my range of cells, but I got there in the end,
Thanks again for taking the time to help me out

Wayne
 
Top