How to Extract Unique List of Names from Column??

[bc4240]

Is there a way to extract a unique list of names from say column C. Each row in the column C contains multiple names separated by commas.

Column C

bob jones, sam spade, tim good
john holmes, bad daddy, roy rogers
bob jones, flash gordon, tim good

results in:

bob jones
sam spade
tim good
john holmes
bad daddy
roy rogers
flash gordon

 

[Eric W]

You'll need VBA for that. Try this:

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. On the sheet that opens, paste this code:

Sub GetUniques()
Dim SrcCol As Range, DstCol As Range, MyDict As Object, MyCol As Variant, i As Long, x As Variant, y As Variant
    
    Set SrcCol = Range("C:C")
    Set DstCol = Range("D:D")
    Set MyDict = CreateObject("Scripting.Dictionary")
    
    MyCol = SrcCol.Resize(SrcCol.Resize(1, 1).Offset(Rows.Count - 1).End(xlUp).Row).Value
    For i = 1 To UBound(MyCol)
        x = Split(MyCol(i, 1), ",")
        For Each y In x
            MyDict(Trim(y)) = 1
        Next y
    Next i
    DstCol.Resize(MyDict.Count) = WorksheetFunction.Transpose(MyDict.keys)
        
End Sub

Change the columns in red to your input and output columns. Switch back to Excel. Press Alt-F8 to open the macro selector. Choose GetUniques and click Run.

Hope this helps.


If you don't want to use VBA, you can select column C, use the Text to Columns tool from the Data tab, then paste columns D and E below C, then use the Remove Duplicates tool. Depends on your particular needs what works best.

 

[Fazza]

Another way for info.


highlight data
Use text to columns to split out the comma delimiting. ALT-D-E-A, etc
cut & paste the results to a single column below a header cell
use TRIM function to take out unwanted spaces
then advanced filter to make wanted list. ALT-D-F-A, etc

 

[bc4240]

Thanks to you both I will try these options

 

[bc4240]

Eric perfect Thanks!

 

[Eric W]

Glad we could help!