从不同行中用逗号分隔的值中获取列中的唯一列表

从不同行中用逗号分隔的值中获取列中的唯一列表

如何获取 EXCEL 中不同行中用逗号分隔的唯一值并将其放入列表

我的数据

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

预期结果:

Dallas
New York
Austin
Tokyo
London
Istanbul

答案1

包含列中的数据A,运行这个简短的 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 作为范围
  昏暗的 c 作为范围
  暗淡如长
  Dim arr() 作为字符串
  Dim dict 作为对象:设置 dict = CreateObject(“Scripting.Dictionary”)
  Dim key 作为变体

  设置 rngData = Range("A14:A17")
  对于 rngData 中的每个 c
    arr = Split(c.值, ",")
    对于 i = 0 至 UBound(arr)
      字典 (Trim (arr (i))) = 1
    下一个
  下一个
  我 = 1
  对于 dict.Keys 中的每个键
    rngData(1).Offset(rngData.Rows.Count + i).Value = key
    我=我+ 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 删除重复项即可。

相关内容