Ok, firstly, here's the function:
Private Function UniqueValues(InputRange As Range) Dim cell As Range Dim tempList As Variant tempList = vbNullString For Each cell In InputRange If cell.Value <> vbNullString Then If InStr(1, tempList, cell.Value) = 0 Then If tempList = vbNullString Then tempList = Trim(CStr(cell.Value)) Else tempList = tempList & "|" & Trim(CStr(cell.Value)) End If Next cell UniqueValues = Split(tempList, "|") End Function
The problem comes when one of the unique values is contained *within* another unique value.
For example, in my column on the worksheet the unique values might be:
The problem with the function above is it effectively builds a string that (should) looks like:
FGHCAD|DES PMO|Admin Support|PMO|Tech Assist
Then Splits the items into an array based on the | character. Whilst this is a very good and clever solution, it has one flaw that I'm not sure how to overcome, namely that because the string "PMO" exists within the string "DES PMO", then item "PMO" never gets added. In other words the temp list ends up as:
FGHCAD|DES PMO|Admin Support|Tech Assist
so that when the Split happens, "PMO" doesn't get added.
Also posted here: http://www.mrexcel.com/forum/e…e-values.html#post4562746