Excel - extract unique values from a column with several values in each cell

Question

I have a list of publishers in a column. I need to extract unique publishers into a separate list, like so:

PLoS
Nature
IMA
NAoS
Elsevier
PLoS
T&F, Ebsco
ONCOTARGET
Oxford, ProQuest
Nature
Elsevier, ProQuest
ACS
Springer
Ebsco/ProQuest
PLoS
Elsevier
Nature

Needs to become:

PLoS
Nature
IMA
NAoS
Elsevier
T&F
Ebsco
ONCOTARGET
Oxford
ProQuest
ACS
Spring

As you can see, in lines where there were several publishers, I need to count them all uniquely.

Is this possible?

Answer 1

This short macro both parses the data and removes duplicates:

Sub dural()
    Dim K As Long, i As Long, N As Long
    Dim ary, a

    K = 1
    N = Cells(Rows.Count, "A").End(xlUp).Row

    ary = Range("A1:A" & N)

    For Each a In ary
        a = Replace(a, ", ", "/")
        If InStr(1, a, "/") > 0 Then
            bry = Split(a, "/")
                For Each b In bry
                    Cells(K, 2) = b
                    K = K + 1
                Next b
        Else
            Cells(K, 2).Value = a
            K = K + 1
        End If
    Next a

    Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub