Hey All,

I have the code below which parses through about 16000 rows with 3 columns. ID/COUNTY/STATE

It output in an adjacent column each ID then each county(s) they are associated with.

Output is ID in Column E and the in Column F, eash STATE.COUNTY seperated by commas..

example:

222 CA.SACRAMENTO, CA.LOS ANGELES, CA.SAN DIEGO
235 NY.SUFFOLK
242 AK.ANCHORAGE

etc..

But for some IDs they have multiple identical STATE.COUNTY data, and I cannot figure out how to retreive only DISTINCT(UNIQUE) STATE.COUNTY values.

example:

221 CA.LOS ANGELES, CA.LOS ANGELES, CA.LOS ANGELES, CA.LOS ANGELES

I would want only:

221 CA.LOS ANGELES

Sub JoinCounties()
Dim dic As Object, rCell As Range, arrKeys, sKey, lRow As Long

Set dic = CreateObject("Scripting.Dictionary")
dic.comparemode = vbTextCompare

' Load the data into a dictionary
For Each rCell In Range("a2", Range("a" & Rows.Count).End(xlUp))
    If Not dic.exists(rCell.Value) Then
          dic.Add rCell.Value, rCell.Offset(, 2).Value & "." & rCell.Offset(, 1).Value
    Else
          dic(rCell.Value) = dic(rCell.Value) & ", " & rCell.Offset(, 2).Value & "." & rCell.Offset(, 1).Value
    End If
Next

' Unload the dictionary
Range("E2").Resize(dic.Count) = Application.WorksheetFunction.Transpose(dic.keys)
For Each rCell In Range("e2", Range("e" & Rows.Count).End(xlUp))
    rCell.Offset(, 1) = dic(rCell.Value)
Next
Set dic = Nothing
End Sub
Hopefully that all makes sense..

Thanks in advance for any help!