Compare in same Col (2003 sp2)

Topic

New Reply

Hello,
I have the following condition: a list of data (alphanumberic). Within the col I would like to determine if there are duplicates and highlight the cell. I understand how to use conditional formatting to check for data next to each other. (cell next to cell). However I do not understand how to compare for match anywhere in the same col.

Thanks,
Brad

Reply | Quote

Replies

Brad,

Is a macro solution ok? I have one that I got from Hans:

Sub SelectDupes_hans()
‘ Source: Woody’s Lounge Post #485599 by Hans
‘ To get the unique entries: change > 1 to = 1, the code will select the
‘ cells containing unique values instead.

Dim oRange As Range
Dim oCell As Range
Dim oSelect As Range
Set oRange = Range(“A27”).CurrentRegion
For Each oCell In oRange.Cells
If Application.WorksheetFunction.CountIf(oRange, oCell) > 1 Then
If oSelect Is Nothing Then
Set oSelect = oCell
Else
Set oSelect = Union(oSelect, oCell)
End If
End If
Next oCell
oSelect.Select
Set oSelect = Nothing
Set oCell = Nothing
Set oRange = Nothing
End Sub

It selects all cells that are duplicates.
Hope this helps.
Chuck

Reply | Quote

This seems to just hang, Not sure why?

Brad

Reply | Quote

Brad,

Not sure why it hung. Did you change the macro to indicate the correct range?
Actually, Since Hans has replied -I would just listen to him…. (smile)

chuck

Reply | Quote

Hans,
the countif worked fine. I will continue to work the macro as well. If nothing else, than to see it work.

Thanks,
brad

Reply | Quote

Here is a solution using conditional formatting. Let’s say that the data are in A1:A100.
Select A1:A100. A1 will probably be the active cell.
Select Format | Conditional Formatting…
Select Formula Is from the first dropdown list.
Enter the following formula in the box next to it:

=COUNTIF($A$1:$A$100,A1)>1

In this formula, A1:A100 is the range (the $ characters are essential here), and A1 is the active cell.
Click Format…
Specify the formatting you want for duplicates.
Click OK twice.

Reply | Quote