☰

Получить уникальный список в столбце из значений, разделенных запятыми в разных строках

tag-icon • tag-icon microsoft-excel
Получить уникальный список в столбце из значений, разделенных запятыми в разных строках

Как получить уникальные значения, разделенные запятыми в разных строках, в списке столбцов в EXCEL?

Мои данные

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

Ожидаемый результат:

Dallas
New York
Austin
Tokyo
London
Istanbul

решение1

С данными в столбцеА, запустите этот короткий макрос 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

введите описание изображения здесь

решение2

Я бы использовал VBA, но доказать это можно с помощью формулы:

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

Это объединит все строки, затем создаст массив всех записей, разделенных на части, ,а затем выполнит цикл, передавая первую найденную запись, которая уже отсутствует в растущем списке.

Пара оговорок:

  1. Для этого требуется Office 365 или более поздняя версия.
  2. Это формула типа массива, поэтому слишком большое ее количество замедлит вычисления.
  3. Его просто трудно поддерживать.
  4. Он должен быть размещен как минимум на одной ячейке выше него, и эта ячейка над адресом должна заменить $B$1:B1внимание к тому, что является абсолютным, а что нет.

введите описание изображения здесь

решение3

Вручную

  1. Данные > Текст по столбцам > Разделители > Далее > Запятая > Готово
  2. Скопировать все данные из отдельных столбцов в один столбец.
  3. Выберите столбец > Данные > Удалить дубликаты.

Автоматически

  1. Откройте свой лист
  2. Alt+F11
  3. Вставить > Модуль
  4. Вставьте этот код:
Подсписок_уникальный()
  Dim rngData как диапазон
  Dim c как диапазон
  Dim i As Long
  Dim arr() как строка
  Измерить dict как объект: Set dict = CreateObject("Scripting.Dictionary")
  Dim key как вариант

  Установить rngData = Диапазон("A14:A17")
  Для каждого c в rngData
    arr = Split(c.Value, ",")
    Для i = 0 до UBound(arr)
      dict(Обрезка(arr(i))) = 1
    Следующий
  Следующий
  я = 1
  Для каждого ключа в словаре Keys
    rngData(1).Смещение(rngData.Строки.Количество + i).Значение = ключ
    я = я + 1
  Следующий

Конец субтитра
  1. В коде замените Range("A14:A17")фактический адрес диапазона, где у вас находятся данные.
  2. УдарятьF5

решение4

Я создал приложение на основе вашего ответа. Это самое простое решение. Просто скопируйте и вставьте ваши данные в текстовое поле и нажмите кнопку «Запустить».

Здесь вы можете увидеть скриншот приложения

Вы также можете изменить разделитель, используя выпадающий список. Дайте мне знать, если у вас есть вопрос.

Вот вам скучный способ сделать это. С помощью 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

Просто удалите дубликаты с помощью Excel.

Связанный контент