Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
wonkywombat
 
Posts: n/a
Default how to find duplicate cells in large array of numbers

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?
  #2   Report Post  
DaveB
 
Posts: n/a
Default

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.

--
Regards,

DaveB


"wonkywombat" wrote:

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?

  #3   Report Post  
DaveB
 
Posts: n/a
Default

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?
--
Regards,

DaveB


"DaveB" wrote:

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.

--
Regards,

DaveB


"wonkywombat" wrote:

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?

  #4   Report Post  
wonkywombat
 
Posts: n/a
Default

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


"DaveB" wrote:

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?
--
Regards,

DaveB


"DaveB" wrote:

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.

--
Regards,

DaveB


"wonkywombat" wrote:

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I copy a LARGE selection of cells from Excel to powerpoint? Deadly Excel Discussion (Misc queries) 2 August 15th 05 03:21 PM
Find - Entire Cells only Andibevan Excel Discussion (Misc queries) 2 April 7th 05 11:45 AM
find a cells from a range of cell kelvintaycc Excel Worksheet Functions 2 April 2nd 05 07:20 PM
How can I have excel search and add multiple cells to find a targe Blakepro Excel Discussion (Misc queries) 1 April 1st 05 02:37 AM
How do I sum an array of cells, even if some of them are #N/A René Excel Discussion (Misc queries) 5 January 18th 05 03:28 PM


All times are GMT +1. The time now is 04:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"