如何获取 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))
这将连接所有的字符串,然后创建一个数组,其中包含所有在,
循环中分割的条目,并传递第一个在增长列表中已经不存在的条目。
几点注意事项:
- 这需要 Office 365 或更高版本
- 它是一个数组类型的公式,因此太多会减慢计算速度。
- 它确实很难维护。
- 它必须放置在其上方至少一个单元格的位置,并且地址上方的单元格应该取代
$B$1:B1
对绝对和不绝对内容的关注。
答案3
手动
- 数据 > 文本到列 > 分隔 > 下一步 > 逗号 > 完成
- 将不同列中的所有数据复制到一列中
- 选择列 > 数据 > 删除重复项
自动地
- 打开你的表格
- Alt+F11
- 插入 > 模块
- 粘贴此代码:
子列表_唯一() 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 下一个 子目录结束
- 在代码中,
Range("A14:A17")
用您拥有数据的实际范围地址进行更改。 - 打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 删除重复项即可。