Finding duplicate numbers

  • Thread starter Thread starter wookmaster
  • Start date Start date
W

wookmaster

Is there a way to make a macro or something that will search through an
excel sheet and see if there are any duplicate numbers. Thanks for any
help
 
assume the numbers are in column A

in B1 put in a formula
=countif(A:A,A1)

then drag fill down the column.

Any cell containing a number larger than 1 contains duplicates.

If you want ignore the first occurance of a number

=if(countif($A$1:A1,A1)>1,"Dup","")

drag fill that down the column B.
 
wookmaster, here is a post by Chip Pearson to do what is selected

Use the following VBA code:

Sub MakeDupsRed()
Dim Rng As Range
For Each Rng In Selection.Cells
If Application.WorksheetFunction.CountIf( _
Selection, Rng) > 1 Then
Rng.Interior.ColorIndex = 3 'red
Else
Rng.Interior.ColorIndex = xlColorIndexNone
End If
Next Rng
End Sub

Select the range of cells and run the macro.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Thanks for the tip and it works a treat. I would like to tweak it a little
and I was wondering is it possible to change the color of the font (text)
instead of highlighting the cells?

Thanks in advance.

M

Paul B said:
wookmaster, here is a post by Chip Pearson to do what is selected

Use the following VBA code:

Sub MakeDupsRed()
Dim Rng As Range
For Each Rng In Selection.Cells
If Application.WorksheetFunction.CountIf( _
Selection, Rng) > 1 Then
Rng.Interior.ColorIndex = 3 'red
Else
Rng.Interior.ColorIndex = xlColorIndexNone
End If
Next Rng
End Sub

Select the range of cells and run the macro.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
Click to expand...
 
Hi Mas
change the lines
....
Rng.Interior.ColorIndex = 3 'red
Else
Rng.Interior.ColorIndex = xlColorIndexNone
....
to
Rng.Font.ColorIndex = 3 'red
Else
Rng.Font.ColorIndex = xlColorIndexNone


HTH
Frank
 
Thanx Frank ...

Just one little problem ... I am getting the error "Unable to set the ColorIndex property

Of the Font class" for the line ... " Rng.Font.ColorIndex = xlColorIndexNone"

Thanx again

Mas
 
Back
Top