excel - 从水平行或字符串中删除重复值

Question

我有一个由大约 50,000 行组成的数据集，每行(或单元格)都有用逗号分隔的值。

item 1, item 2, item 1, item 1, item3, item 2, item 4, item3

目标输出很简单

item 1, item 2, item3, item 4

我可以使用 excel、open office calc、notepad++ 或任何其他免费程序(我找到了一个 javascript 解决方案，但它是针对单个字符串的，尝试运行它 50,000 次要么不起作用，要么会失败比我长，而且我不知道足够的 JS 来调整它)

关于如何做到这一点有什么建议吗？

<编辑后注意到某些项目将包含空格>

Answer 1

应该可以帮助你开始。关闭屏幕更新和计算以获得更好的性能...

Sub Tester()

    Dim dict As Object
    Dim arrItems, c As Range, y As Long
    Dim val

    Set dict = CreateObject("scripting.dictionary")

    For Each c In ActiveSheet.Range("A1:A100").Cells

        arrItems = Split(c.Value, ",")
        dict.RemoveAll
        For y = LBound(arrItems) To UBound(arrItems)
            val = Trim(arrItems(y))
            If Not dict.exists(val) Then dict.Add val, 1
        Next y

        c.Offset(0, 1).Value = Join(ArraySort(dict.keys), ",")

    Next c

End Sub

用于对键进行排序:

Function ArraySort(MyArray As Variant)

    Dim First           As Integer
    Dim Last            As Integer
    Dim i               As Integer
    Dim j               As Integer
    Dim Temp

    First = LBound(MyArray)
    Last = UBound(MyArray)
    For i = First To Last - 1
        For j = i + 1 To Last
            If MyArray(i) > MyArray(j) Then
                Temp = MyArray(j)
                MyArray(j) = MyArray(i)
                MyArray(i) = Temp
            End If
        Next j
    Next i
    ArraySort = MyArray

End Function