Public Sub RemoveDupEntries()
Dim i As Long, _
j As Long, _
LR As Long, _
temparr As Variant, _
temp As String, _
delim As String
'Change application settings to speed up code
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
' Variable Initialization
delim = ","
LR = Range("H" & Rows.Count).End(xlUp).Row
'Loop through the range
For i = 2 To LR
'Displays a visual progression indicator in the status bar.
Application.StatusBar = "Currently checking row " & i & " of " & LR
'Check to see if current cell is populated
If Len(Range("H" & i).Value) > 0 Then
'Create a 1-dimensional array using the Split function, which splits the
' current cell's value into separate entries based on the delimiter.
' Value: Current Cell
' Delimiter: delim variable (defined in the variable initialization)
temparr = Split(Range("H" & i).Value, delim)
'Loop through the array created by the Split function
For j = LBound(temparr) To UBound(temparr)
'Check to see if the current element of the array is already a part of the temp
' variable. If it is not a part of the temp variable, then add it
If InStr(temp, temparr(j)) = 0 Then
temp = temp & temparr(j) & delim & " "
End If
Next j
'Removes the two right characters of the temp variable if needed prior to storing it in the current cell
If Right$(temp, 2) = delim & " " Then
Range("H" & i).Value = Left$(temp, Len(temp) - 2)
Else
Range("H" & i).Value = temp
End If
End If
'Removes all entries from the array created by Split, and resets temp to a null string
Erase temparr
temp = ""
Next i
'Reset various application settings
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.StatusBar = False
End With
End Sub
