Formula that would highlight two identical cells..
 

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

Formula that would highlight two identical cells..
 

Try something like this:

Select A1:A500, with A1 as the active cell
FormatConditional Format
Formula is: =COUNTIF($A$1:A$500,A1)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-Pro


"Monk" wrote:

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

Formula that would highlight two identical cells..
 

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




"Ron Coderre" wrote:

Try something like this:

Select A1:A500, with A1 as the active cell
FormatConditional Format
Formula is: =COUNTIF($A$1:A$500,A1)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-Pro


"Monk" wrote:

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

Formula that would highlight two identical cells..
 

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

"Ron Coderre" wrote:

Try something like this:

Select A1:A500, with A1 as the active cell
FormatConditional Format
Formula is: =COUNTIF($A$1:A$500,A1)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-Pro


"Monk" wrote:

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

Formula that would highlight two identical cells..
 

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)1

D10: MyList (the same col heading as A10)

Select your list (A10:A500)
Then....from the Excel Main Menu....
<Data<Filter<Advanced Filter
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)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 "CopyTo" range.

Post back with any other questions.

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

XL2002, WinXP


"tdfugere" wrote:

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

"Ron Coderre" wrote:

Try something like this:

Select A1:A500, with A1 as the active cell
FormatConditional Format
Formula is: =COUNTIF($A$1:A$500,A1)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-Pro


"Monk" wrote:

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

Formula that would highlight two identical cells..
 

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

Thank you
Gregory

"Monk" wrote:

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