Cecking for duplicates

[MJKelly]

Hi,

I have a column of data which needs to be checked for duplicates, how
would I do this with a macro? The data is a list of names which have
been assigned to tasks. I can't have the same person assigned to two
tasks/jobs. Would be great if the duplicates are shown in a different
colour once the macro is finished so I can make the necessary changes.

hope you can help.
Matt

 

[Leanne]

Hi,
I do not know of a macro but have previously been refered to the following
address where a duplicate add in has been created. To my knowledge however it
has not been tested. Either way there is a good amount of information on
this site that will help you.

http://www.mvps.org/dmcritchie/excel/duplicates.htm

 

[Rick Rothstein \(MVP - VB\)]

It sounds like Conditional Formatting is what you want. Give this a try.
Assuming your names are in Column B and you want duplicated names
highlighted. Also, let's assume the first name is placed in B2 (where B1 is
assumed to be for a header). Click in B2 and scroll down so as to select as
many rows as you think you will ever need at maximum (it is important that
you start in B2). Now, click Format/Conditional Formatting in Excel's menu
bar. On the dialog box that appears, set the first drop down to "Formula Is"
and put this formula in the second field...

=COUNTIF(B:B,B2)>1

Next, click the Format button on the dialog, click the Patterns tab and pick
a highlight color. OK your way back to the worksheet. Any duplicates within
the cells that were selected when you clicked on the menu bar will be
highlighted in the color you selected.

Rick

 

[MJKelly]

Rick,

The conditional formatting works fine, but I need to check each cell
in the column (say 200 rows), to find any instances of duplication,
not just a duplication of the value in cell B2.
Any idea's?
Matt

 

[Nigel]

This code sets up a conditional format for column A (names in A2 onwards), I
set limit to row 500, change as required. Note: xl2007 version used.

Sub DuplicateConditional()
With Range("A2:A500") ' < set limit of your range here
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(A:A,A2)>1"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Bold = True
.Color = -16776961
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With
End Sub

 

[MJKelly]

Actually,

The link in Leanne's reply has solved the problem. That's great,
thanks a million.

Matt

 

[MJKelly]

Nigel,

I am using Excel 2000. The code is stopping part way through. Can
you post an Excel 2000 version?
I want to run a macro to check the data as needed instead of applying
cond form to large areas of the worksheet.

Thanks,
Matt

 

[kounoike]

one way

Macro Highlightdup below sorts rows by names and highlight duplicate names
if any. next run macro named Reset after you make the necessary changes.
then you will get orignal format.
assuming your names header reside in A1. if this was not the case, change
the code in Highlightdup Set start = Range("A1") to your case. but i tested
this in excel2003, so i'm not sure this would work in excel2000.

Sub Highlightdup()
Dim start As Range, last As Range, st1 As Range, st2 As Range
Dim aux As Range
Dim i As Long, nco As Long, colr As Long

Set start = Range("A1")
Set last = start.End(xlDown)

start.EntireColumn.Insert
Set aux = start.Cells(1, 0).EntireColumn. _
Resize(last.Row - start.Row + 1)
aux.Cells(1, 1) = "no" & Date
For i = 1 To last.Row - start.Row
aux.Cells(i + 1, 1) = i
Next
start.CurrentRegion.Sort Key1:=start, Order1:=xlAscending, _
Header:=xlGuess
Set st1 = start.Cells(2, 1)
Set st2 = st1.Cells(2, 1)
Do While (st1 <> "")
If st1 = st2 Then
Set st2 = st2.Cells(2, 1)
Else
If st2.Row - st1.Row = 1 Then
Set st1 = st2
Set st2 = st1.Cells(2, 1)
Else
colr = IIf(nco Mod 2 = 0, 8, 6)
Range(st1, st2.Cells(0, 1)).Interior. _
ColorIndex = colr
nco = nco + 1
Set st1 = st2
Set st2 = st1.Cells(2, 1)
End If
End If
Loop
End Sub

Sub Reset()
Dim start

Set start = Cells.Find("no" & Date, lookat:=xlWhole)
If start Is Nothing Then
MsgBox "no" & Date & " was not found"
Exit Sub
End If
start.CurrentRegion.Sort Key1:=start, Order1:=xlAscending, _
Header:=xlGuess
start.Cells(1, 2).EntireColumn.Interior.ColorIndex = xlNone
start.EntireColumn.Delete
End Sub

keiji

 

[Rick Rothstein \(MVP - VB\)]

That is what the procedure I outlined for you will do.... it highlights any
and all duplicates in the range you selected to apply the Conditional
Formatting to. The B2 in the formula I posted must match with the active
cell in the selection (which is why I told you to start in B2 and drag
down... doing that makes B2 the active cell in the selection); after that,
the Conditional Formatter will apply the formula relatively throughout the
range. Give the procedure I outlined a try... unless I misunderstood what
you are looking for, it does what I think you asked for.

Rick