酷徒LOGO

从以逗号分隔的列中获取唯一列表


问题:

如何获取唯一值,这些值以不同行的逗号分隔在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



enter image description here


回答 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))



这个将创建所有的字符串,然后创建在 , 上分割的所有条目,然后循环遍历已经在增长列表中不存在的第一个。

一对 caveates:

  • 这需要 Office 365或者更高版本
  • 它是一个 array 类型公式,这样太多会减慢calcs的速度。
  • 只是很难维护。
  • 它必须放置在上面的至少一个单元格上,以上地址的细胞应该代替 $B$1:B1 以及什么不绝对。
  • enter image description here


回答 3:

手工

  • 数据> 文本到列> 分隔> 下一个> 逗号> 完成
  • 将各个列中的所有数据复制到一个列中
  • 选择列> 数据> 删除重复项
  • 自动自动调整

  • 打开工作表
  • Alt+F11
  • 插入> 模块
  • 粘贴以下代码:
  • Sub list_unique()
     Dim rngData As Range
     Dim c As Range
     Dim i As Long
     Dim arr() As String
     Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
     Dim key As Variant
     Set rngData = Range("A14:A17")
     For Each c In rngData
     arr = Split(c.Value,",")
     For i = 0 To UBound(arr)
     dict(arr(i)) = 1
     Next
     Next
     i = 1
     For Each key In dict.Keys
     rngData(1).Offset(rngData.Rows.Count + i).Value = Trim(key)
     i = i + 1
     Next
    End Sub
  • 在代码中,用你的实际范围地址更改 Range("A14:A17"),在这里你有你的数据。
  • 按 F5





Copyright © 2011 HelpLib All rights reserved.    知识分享协议 京ICP备17041772号-2  |  如果智培  |  酷兔英语  |  帮酷