excel 删除重复数据而不盘点顺序

bd1hkmkf  于 2023-01-21  发布在  其他
关注(0)|答案(2)|浏览(140)

我有以下数据

0/3, 1/1, 3/4
1/3, 3/2, 6/2
12/1, 3/6, 3/4
3/4, 0/3, 1/1     'will be considered is duplicate with the first data

有没有办法找到并删除这样的重复数据?
我目前的方法是基于“”拆分为3个字符串,然后检查以下条件。

'I got each String value by mid command.
'FrstStr1: First String of String 1
'SecStr1: Second String of String 1
'ThrStr1: Third String of String 1
'FrstStr2: First String of String 2
'SecStr2: Second String of String 2
'ThrStr2: Third String of String 2

if (FrstStr1 = FrstStr2 and SecStr1 = SecStr2 and ThrStr1 = ThrStr2) or  
  (FrstStr1 = FrstStr2 and SecStr1 = ThrStr2 and ThrStr1 = SecStr2) or  
  () or () .... then

我列出了6种可能的情况,并把他们放在如果条件像上面。

mrwjdhj3

mrwjdhj31#

1.用逗号分隔符拆分数据生成数组。
1.和按函数对数组排序。
1.按字典检查重复数据。

##代码##

Sub test()
    Dim vR(), vDB
    Dim dic As Object
    Dim v As Variant
    Dim s As String
    Dim i As Long, n As Long

    Set dic = CreateObject("Scripting.Dictionary")

    vDB = Range("a1").CurrentRegion

    For i = 1 To UBound(vDB, 1)
        v = Split(vDB(i, 1), ",")
        s = newArray(v)
        If dic.exists(s) Then
        Else
            dic.Add s, s
            n = n + 1
            ReDim Preserve vR(1 To n)
            vR(n) = vDB(i, 1)
        End If
    Next i
    If n Then
        Range("e1").Resize(n) = WorksheetFunction.Transpose(vR)
    End If

End Sub
Function newArray(v As Variant)
    Dim temp As String
    Dim r As Integer, i As Integer, j As Integer

    r = UBound(v)

    For i = LBound(v) To r - 1
        For j = i + 1 To r
            v(i) = Trim(v(i))
            v(j) = Trim(v(j))
            If v(i) > v(j) Then
                temp = v(j)
                v(j) = v(i)
                v(i) = temp
            End If
        Next j
    Next i
    newArray = Join(v, ",")
End Function

图像

zynd9foi

zynd9foi2#

DictionaryArrayList对象的外推可以导致非常紧凑(并且可维护)的代码:

Sub RemoveDuplicatedDataWithoutCountingOrder()

    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

    Dim j As Long
    Dim key As String
    Dim datum As Variant, couple As Variant
    For Each datum In Range("A1").CurrentRegion.Value
        key = vbNullString
        With CreateObject("System.Collections.SortedList")
            For Each couple In Split(Replace(datum, " ", vbNullString), ",")
                .Add couple, 0
            Next
            For j = 0 To .Count - 1
                key = key & .getkey(j)
            Next
            If Not dict.exists(key) Then dict.Add key, datum
        End With
    Next

    Range("C1").Resize(dict.Count) = Application.Transpose(dict.items)

End Sub

相关问题