This is an adaptation of John Walkenbach's tip
http://j-walk.com/ss/excel/tips/tip47.htm

Output is sent to the immediate window. TestUniques generates and array
similar to the one you describe. The routine uses Split, so it runs in
xl2000 or later.


Option Explicit
' This example is based on a tip by J.G. Hussey,
' published in "Visual Basic Programmer's Journal"

Sub TestUniques()
Dim varr As Variant
Dim i As Long, j As Long
Dim sStr As String
Dim varr1 As Variant
ReDim varr(1 To 10)
For i = 1 To 10
sStr = ""
For j = 1 To Int(Rnd * 10 + 1)
sStr = sStr & Chr(Int(Rnd * 26 + 65)) _
& Chr(Int(Rnd * 26 + 65)) & " "
Next
varr(i) = Trim(sStr)
Next
varr1 = Uniques(varr)
For i = LBound(varr1) To UBound(varr1)
Debug.Print varr1(i)
Next
End Sub

Function Uniques(varr)
Dim AllCells As Variant, varr1 As Variant
Dim varr2 As Variant, varr3 As Variant
Dim NoDupes As New Collection
Dim i As Long, j As Long, k As Long
Dim Swap1, Swap2, Item

' The items are in A1:A105
varr1 = varr
' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For i = LBound(varr1) To UBound(varr1)
varr3 = Split(varr1(i))
For k = LBound(varr3) To UBound(varr3)
NoDupes.Add varr3(k), CStr(varr3(k))
Next
' Note: the 2nd argument (key) for the Add method must be a string
Next i

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
ReDim varr2(1 To NoDupes.Count)
i = 1
For Each Item In NoDupes
varr2(i) = Item
i = i + 1
Next Item

Uniques = varr2
End Function



--
Regards,
Tom Ogilvy




"Johnny Daly" wrote in message
...
Hi,

I have an array of data with various combinations of two-
letter IDs for each observation. For instance, observation
1 has the following 6 IDs "FO CH GM HO VW MI" (in that
format), observation 2 has just 1 ID "GM", observation 3
has 4 IDs "NI VW MI CH" and so forth. In theory, there is
no limit for how many IDs an observation can have, but
glancing down the list, it seems that no observation has
more than 10 IDs. Is there a method of generating a list
of all the IDs that appear in the list, so that this list
would only show each ID once?

Thanks!