Erhalten Sie eine eindeutige Liste in einer Spalte aus durch Kommas getrennten Werten in verschiedenen Zeilen

Question

Wie bekomme ich in EXCEL eindeutige Werte, die durch Kommas getrennt sind, in verschiedene Zeilen in eine Spaltenliste?

Meine Daten

Dallas, New York, Austin, Tokyo
Dallas, New York, Austin, Tokyo
London, Tokyo
Tokyo, Istanbul

Erwartetes Ergebnis:

Dallas
New York
Austin
Tokyo
London
Istanbul

Answer 1

Mit Daten in der SpalteA, führen Sie dieses kurze VBA-Makro aus:

Sub Sundar()
    Dim s As String, c As Collection, k As Long

    Set c = New Collection
    k = 1

    s = Replace(Application.WorksheetFunction.TextJoin(",", True, Range("A:A")), " ", "")
    arr = Split(s, ",")

    On Error Resume Next
    For Each a In arr
        c.Add a, CStr(a)
        If Err.Number = 0 Then
            Cells(k, 2).Value = a
            k = k + 1
        Else
            Err.Number = 0
        End If
    Next a
    On Error GoTo 0
End Sub

Answer 2

Ich würde VBA verwenden, aber um zu beweisen, dass es mit einer Formel geht:

=INDEX(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",,$A$2:$A$5),",",REPT(" ",99)),(ROW($XFD$1:INDEX($XFD:$XFD,LEN(TEXTJOIN(",",,$A$2:$A$5))-LEN(SUBSTITUTE(,",",""))+1))-1)*99+1,99)),AGGREGATE(15,7,ROW($XFD$1:INDEX($XFD:$XFD,LEN(TEXTJOIN(",",,$A$2:$A$5))-LEN(SUBSTITUTE(,",",""))+1))/(COUNTIFS($B$1:B1,TRIM(MID(SUBSTITUTE(TEXTJOIN(",",,$A$2:$A$5),",",REPT(" ",99)),(ROW($XFD$1:INDEX($XFD:$XFD,LEN(TEXTJOIN(",",,$A$2:$A$5))-LEN(SUBSTITUTE(,",",""))+1))-1)*99+1,99)))=0),1))

Dadurch werden alle Zeichenfolgen zusammengeführt und ein Array aller Einträge erstellt, das aufgeteilt wird. ,Anschließend wird der erste gefundene Eintrag, der in der wachsenden Liste noch nicht vorhanden ist, durchgegangen.

Ein paar Vorbehalte:

1. Dies erfordert Office 365 oder höher
2. Es handelt sich um eine Formel vom Array-Typ, und zu viele davon verlangsamen daher die Berechnungen.
3. Es ist einfach schwer zu pflegen.
4. Es muss mit mindestens einer Zelle darüber platziert werden und diese Zelle über der Adresse sollte die $B$1:B1Beachtung dessen ersetzen, was absolut ist und was nicht.

Answer 3

Manuell

1. Daten > Text in Spalten > Getrennt > Weiter > Komma > Fertig
2. Alle Daten aus einzelnen Spalten in eine Spalte kopieren
3. Wählen Sie die Spalte > Daten > Duplikate entfernen

---

Automatisch

1. Öffnen Sie Ihr Blatt
2. Alt+F11
3. Einfügen > Modul
4. Fügen Sie diesen Code ein:

Unterliste_eindeutig()
  Dim rngData als Bereich
  Dim c als Bereich
  Dim i As Long
  Dim arr() As String
  Dim dict als Objekt: Setze dict = CreateObject("Scripting.Dictionary")
  Dim-Taste als Variante

  Setze rngData = Range("A14:A17")
  Für jedes c in rngData
    arr = Split(c.Wert, ",")
    Für i = 0 bis UBound(arr)
      dict(Trim(arr(i))) = 1
    Nächste
  Nächste
  ich = 1
  Für jeden Schlüssel in dict.Keys
    rngData(1).Offset(rngData.Rows.Count + i).Wert = Schlüssel
    ich = ich + 1
  Nächste

End Sub

5. Ersetzen Sie den Code Range("A14:A17")durch die tatsächliche Bereichsadresse, in der sich Ihre Daten befinden.
6. SchlagF5

Answer 4

Ich habe eine Anwendung basierend auf Ihrer Antwort erstellt. Dies ist die einfachste Lösung. Kopieren Sie einfach Ihre Daten, fügen Sie sie in den Textbereich ein und klicken Sie auf die Schaltfläche „Ausführen“.

Hier sehen Sie einen Screenshot der Anwendung

Sie können das Trennzeichen auch über die Kombinationsbox ändern. Wenn Sie eine Frage haben, lassen Sie es mich wissen.

Hier ist die langweilige Art, es zu tun. Mit VBA.

Sub Macro1()
Dim countries As String
Dim arrayofcountries
Dim con As Integer

con = 0

For i = 2 To 5
countries = Cells(i, 1).Value

If (countries = "") Then
    ''Do nothing

Else
    arrayofcountries= Split(countries , ",")
    For Z = LBound(arrayofcountries) To UBound(arrayofcountries)

                        Cells(i + con, 3).Value = arrayofcountries(Z)
                        con = con + 1

    Next Z
 
End If

con = con - 1
Next i

End Sub

Entfernen Sie Duplikate einfach mit Excel.