Obtenha uma lista exclusiva em uma coluna a partir de valores separados por vírgulas em linhas diferentes

Question

Como posso obter valores únicos separados por vírgulas em linhas diferentes em uma lista de colunas no EXCEL

Meus dados

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

Resultado esperado:

Dallas
New York
Austin
Tokyo
London
Istanbul

Answer 1

Com dados na colunaA, execute esta pequena macro VBA:

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

Eu usaria vba, mas para provar isso pode ser feito com uma fórmula:

=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))

Isso juntará todas as strings e criará um array de todas as entradas divididas no ,ciclo, passando pela primeira a ser encontrada que já não existe na lista crescente.

Algumas advertências:

1. Isso requer o Office 365 ou posterior
2. É uma fórmula do tipo array e, como tal, muitos retardarão os cálculos.
3. É simplesmente difícil de manter.
4. Deve ser colocado com pelo menos uma célula acima e essa célula acima do endereço deve substituir a $B$1:B1atenção ao que é e ao que não é absoluto.

Answer 3

Manualmente

1. Dados > Texto para Colunas > Delimitado > Próximo > Vírgula > Concluir
2. Copie todos os dados de colunas separadas em uma coluna
3. Selecione a coluna > Dados > Remover Duplicados

---

Automaticamente

1. Abra sua planilha
2. Alt+F11
3. Inserir > Módulo
4. Cole este código:

Sublista_unique()
  Dim rngData como intervalo
  Dim c como intervalo
  Dim i enquanto
  Dim arr() como string
  Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
  Dim key como variante

  Definir rngData = Range("A14:A17")
  Para cada c em rngData
    arr = Dividir(c.Valor, ",")
    Para i = 0 para UBound (arr)
      dict(Trim(arr(i))) = 1
    Próximo
  Próximo
  eu = 1
  Para cada chave em dict.Keys
    rngData(1).Offset(rngData.Rows.Count + i).Value = chave
    eu = eu + 1
  Próximo

Finalizar sub

5. No código, altere Range("A14:A17")com o endereço do intervalo real onde você tem seus dados.
6. BaterF5

Answer 4

Criei um aplicativo com base na sua resposta. Esta é a solução mais fácil. Basta copiar e colar seus dados na área de texto e clicar no botão Executar.

Aqui você pode ver uma captura de tela do aplicativo

Você também pode alterar o separador usando a caixa de combinação. Deixe-me saber se você tiver alguma dúvida.

Aqui você tem a maneira chata de fazer isso. Usando um 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

Basta remover duplicatas com o Excel.