software

Hi..,
I have a lengthy list of cells with numbers within them (say A1:A500) and
wish to have a formula which would instantly detect if any of the cells
contains the same number (duplicate). Ideally the formula would return a
‘Duplicate Number’ message within a specified cell (say C1) and if possible
highlighting both of the duplicated cells in a different colour.

Thanks in advance,
Monk

Try something like this:

Select A1:A500, with A1 as the active cell
Formatgt;Conditional Formatgt;
Formula is: =COUNTIF($A$1:A$500,A1)gt;1
Click the [Format...] button
Set your format (I usually set the pattern color to green)
Click the [OK] buttons

Done

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Monkquot; wrote:

gt; Hi..,
gt; I have a lengthy list of cells with numbers within them (say A1:A500) and
gt; wish to have a formula which would instantly detect if any of the cells
gt; contains the same number (duplicate). Ideally the formula would return a
gt; ‘Duplicate Number’ message within a specified cell (say C1) and if possible
gt; highlighting both of the duplicated cells in a different colour.
gt;
gt; Thanks in advance,
gt; Monk

Hi Ron,
This seems to work just fine..
Thanks..
M

quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; Select A1:A500, with A1 as the active cell
gt; Formatgt;Conditional Formatgt;
gt; Formula is: =COUNTIF($A$1:A$500,A1)gt;1
gt; Click the [Format...] button
gt; Set your format (I usually set the pattern color to green)
gt; Click the [OK] buttons
gt;
gt; Done
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Monkquot; wrote:
gt;
gt; gt; Hi..,
gt; gt; I have a lengthy list of cells with numbers within them (say A1:A500) and
gt; gt; wish to have a formula which would instantly detect if any of the cells
gt; gt; contains the same number (duplicate). Ideally the formula would return a
gt; gt; ‘Duplicate Number’ message within a specified cell (say C1) and if possible
gt; gt; highlighting both of the duplicated cells in a different colour.
gt; gt;
gt; gt; Thanks in advance,
gt; gt; Monk

Is there a way to pull the duplicate cells, as well as info from the rows in
which the duplicate cells lie, and extract it into a report (without having
to cut and paste)?

Thanks
tdfugere

quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; Select A1:A500, with A1 as the active cell
gt; Formatgt;Conditional Formatgt;
gt; Formula is: =COUNTIF($A$1:A$500,A1)gt;1
gt; Click the [Format...] button
gt; Set your format (I usually set the pattern color to green)
gt; Click the [OK] buttons
gt;
gt; Done
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Monkquot; wrote:
gt;
gt; gt; Hi..,
gt; gt; I have a lengthy list of cells with numbers within them (say A1:A500) and
gt; gt; wish to have a formula which would instantly detect if any of the cells
gt; gt; contains the same number (duplicate). Ideally the formula would return a
gt; gt; ‘Duplicate Number’ message within a specified cell (say C1) and if possible
gt; gt; highlighting both of the duplicated cells in a different colour.
gt; gt;
gt; gt; Thanks in advance,
gt; gt; Monk

This example skips the original values and uses Advanced Filter to build a
separate list of the duplicates (instead of listing every dupe multiple
times):

With your list is in A10:A500, with A10 as the Heading: MyList

A1: Test (or blank or any other value that does not match a column heading)
A2: =COUNTIF(A$10:A11,A11)gt;1

D10: MyList (the same col heading as A10)

Select your list (A10:A500)
Then....from the Excel Main Menu....
lt;Datagt;lt;Filtergt;lt;Advanced Filtergt;
List Range: (already selected $A$10:$A$500)
Criteria Range: $A$1:$A$2

Click the [OK] button to filter the list in place.
OR......
To copy the duplicates to another area:
Check: Copy to another location
Copy to: D10 (which contains the col heading: MyList)
Check: Unique Records
Click the [OK] button

NOTE_1: For duplicated values, if you really do want to extract original
values AND duplicates, change the criteria formula to this
A2: =COUNTIF(A$10:$A$500,A11)gt;1

NOTE_2: If there are additional columns in your list range and you want to
pull that data too.....Include those cells in the list to be filtered AND
include the column heading in the quot;CopyToquot; range.

Post back with any other questions.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXPquot;tdfugerequot; wrote:

gt; Is there a way to pull the duplicate cells, as well as info from the rows in
gt; which the duplicate cells lie, and extract it into a report (without having
gt; to cut and paste)?
gt;
gt; Thanks
gt; tdfugere
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; Select A1:A500, with A1 as the active cell
gt; gt; Formatgt;Conditional Formatgt;
gt; gt; Formula is: =COUNTIF($A$1:A$500,A1)gt;1
gt; gt; Click the [Format...] button
gt; gt; Set your format (I usually set the pattern color to green)
gt; gt; Click the [OK] buttons
gt; gt;
gt; gt; Done
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Monkquot; wrote:
gt; gt;
gt; gt; gt; Hi..,
gt; gt; gt; I have a lengthy list of cells with numbers within them (say A1:A500) and
gt; gt; gt; wish to have a formula which would instantly detect if any of the cells
gt; gt; gt; contains the same number (duplicate). Ideally the formula would return a
gt; gt; gt; ‘Duplicate Number’ message within a specified cell (say C1) and if possible
gt; gt; gt; highlighting both of the duplicated cells in a different colour.
gt; gt; gt;
gt; gt; gt; Thanks in advance,
gt; gt; gt; Monk

Hi Did you get the answer for this if no
you can write to me on my email address
which is

Thank you
Gregory

quot;Monkquot; wrote:

gt; Hi..,
gt; I have a lengthy list of cells with numbers within them (say A1:A500) and
gt; wish to have a formula which would instantly detect if any of the cells
gt; contains the same number (duplicate). Ideally the formula would return a
gt; ‘Duplicate Number’ message within a specified cell (say C1) and if possible
gt; highlighting both of the duplicated cells in a different colour.
gt;
gt; Thanks in advance,
gt; Monk