Karl Burrows wrote...
It is in a database, but am using Excel to pull some formatted reports.
Here is a better example, for multiple builders:

Ryan
Ryan Townhomes
Ryan 60'
Mulvaney - Greenbrier
Mulvaney - 80
KB Home
KB Home 70'

I need to extract the unique values to give me:

Ryan
Mulvaney
KB Home
....

It's not too difficult to get Ryan and KB Home using the following udf,
which returns an array.


Function foo(r As Range) As Variant
Dim d As Object
Dim c As Variant, x As Variant, t As String

Set d = CreateObject("Scripting.Dictionary")

For Each c In r
t = c.Text
If d.Exists(t) Then d.Item(t) = d.Item(t) + 1 _
Else d.Add Key:=t, Item:=1
Next c

For Each c In d.Keys
For Each x In d.Keys
If x < c And x Like c & "*" Then d.Remove Key:=x
Next x
Next c

foo = Application.WorksheetFunction.Transpose(d.Keys)
End Function


But reducing out Mulvaney is much more difficult in general because
left substrings could be common to several distinct records, e.g.,

John Smith Builders
John Smith & Sons Construction

There may be approaches you could take using fuzzy string matching, but
you may find it expedient to use the udf above to filter out most
'duplicates', then remove the remaining ones manually.