I have a large data set in which Column A specifies a Group name and column D specifies a number that needs to be unique PER group.
I'd like to use VBA to highlight any Duplicate cells in Column D, for same Groups from Column A.
Ex:
In this case, cells for Group 1 containing "20" would be highlighted and cells for Group 2 containing "33" would be highlighted.
A | B | C | D |
---|---|---|---|
Group 1 | 20 | ||
Group 1 | 15 | ||
Group 1 | 12 | ||
Group 1 | 16 | ||
Group 1 | 2 | ||
Group 1 | 20 | ||
Group 2 | 1 | ||
Group 2 | 33 | ||
Group 2 | 18 | ||
Group 2 | 15 | ||
Group 2 | 4 | ||
Group 2 | 33 |
Modifying my linked post:
Sub HiliteDups()
Const HILITE_COLOR = vbRed
Dim ws As Worksheet, k
Dim dict As Object, rng As Range, data, r As Long
'Set dict = CreateObject("scripting.dictionary") 'Windows
Set dict = New Dictionary 'Cross-platform
' (https://github.com/VBA-tools/VBA-Dictionary)
Set ws = ActiveSheet 'for example
Set rng = ws.Range("A2:D" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
rng.Columns(4).Interior.ColorIndex = xlNone 'clear flags
data = rng.Value 'get data as array (for faster looping)
For r = 1 To UBound(data, 1) 'loop over range data
k = data(r, 1) & "<>" & data(r, 4) 'composite key A+D
If dict.Exists(k) Then
If dict(k) > 0 Then 'need to color the first row for this key?
rng.Cells(dict(k), 4).Interior.Color = HILITE_COLOR
dict(k) = 0
End If
rng.Cells(r, 4).Interior.Color = HILITE_COLOR 'color this row
Else
dict.Add k, r 'remember this row in case there are duplicates later
End If
Next r
End Sub
EDIT - allow for use of cross-platform dictionary class from:
https://github.com/VBA-tools/VBA-Dictionary
And another alternative: using a Collection with keys
Sub HiliteDups()
Const HILITE_COLOR = vbRed
Dim ws As Worksheet, k As String, valA, valD
Dim col As Collection, rng As Range, data, r As Long, v
Set ws = ActiveSheet 'for example
Set rng = ws.Range("A2:D" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
rng.Columns(4).Interior.ColorIndex = xlNone 'clear flags
data = rng.Value 'get data as array (for faster looping)
Set col = New Collection 'for tracking unique A+D combinations
For r = 1 To UBound(data, 1) 'loop over range data
valA = data(r, 1)
valD = data(r, 4)
If Len(valA)>0 And Len(valD)>0 Then
k = valA & "<>" & valD 'composite key A+D
v = KeyValue(col, k)
If Not IsEmpty(v) Then
If v > 0 Then 'need to color the first row for this key?
rng.Cells(v, 4).Interior.Color = HILITE_COLOR
col.Remove k 'remove keyed value
col.Add 0, k 're-add as zero
End If
rng.Cells(r, 4).Interior.Color = HILITE_COLOR 'color this row
Else
col.Add Item:=r, Key:=k 'remember this row and key in case there are duplicates later
End If 'key was found?
End If 'values in A and D?
Next r
End Sub
'Retrieve value for key `k` from collection `col`
' Returns Empty if there's no such key
Function KeyValue(col As Collection, k As String)
On Error Resume Next 'ignore error if no match for `k`
KeyValue = col.Item(k)
End Function