|
不要嫌我啰嗦啊嘿嘿,这个题我也学到了很多,给你看下另外两种方法,船长那个思路的速度最快
方法二思路:先排序,再循环判断
- Sub 查询重复中的不重复记录2()
- Dim Arr(), Brr(), MyR&, MyC&, k&, s&, T
- T = Timer
- Application.ScreenUpdating = False
- Arr = Sheets("基本信息").UsedRange.Value
- Sheets("重复").Columns("G").NumberFormatLocal = "@"
- Sheets("重复").Range("E1").Resize(UBound(Arr), UBound(Arr, 2)) = Arr
- ReDim Brr(1 To UBound(Arr), 1 To 3)
- Worksheets("重复").Select
- With Worksheets("重复").Sort
- .SortFields.Clear
- .SortFields.Add Key:=Range("F1"), Order:=xlDescending
- .SetRange Range("E1:G" & Cells(Rows.Count, 7).End(xlUp).Row)
- .Header = xlYes
- .Apply
- End With
- Arr = Sheets("重复").Range("e1:g" & Cells(Rows.Count, 7).End(xlUp).Row).Value
- For MyR = 2 To UBound(Arr)
- If Arr(MyR, 3) = Arr(MyR - 1, 3) Then
- k = k + 1
- If k = 1 Then
- s = s + 1
- For MyC = 1 To 3
- Brr(s, MyC) = Arr(MyR - 1, MyC)
- Next MyC
- End If
- Else
- k = 0
- End If
- Next MyR
- With Sheets("重复")
- .Columns("d").NumberFormatLocal = "@"
- .Range("B2").Resize(s, 3) = Brr
- .Columns("E:G").Clear
- End With
- MsgBox "用时:" & Format(Timer - T, "0.00秒")
- Application.ScreenUpdating = True
- End Sub
复制代码 方法三思路:双字典单循环(船长提供)
- Sub 提取重复数据中的不重复记录3()
- Dim dic1 As Object, dic2 As Object, Arr(), Brr(), MyR&, MyC%, k&, T
- T = Timer
- Application.ScreenUpdating = False
- Set dic1 = CreateObject("scripting.dictionary")
- Set dic2 = CreateObject("scripting.dictionary")
- Arr = Sheets("基本信息").Range("b1:d" & Sheets("基本信息").Cells(Rows.Count, 4).End(xlUp).Row).Value
- ReDim Brr(1 To UBound(Arr), 1 To 3)
- For MyR = 1 To UBound(Arr)
- If dic1.Exists(Arr(MyR, 3)) Then
- If Not dic2.Exists(Arr(MyR, 3)) Then '重复中出现第一次
- k = k + 1
- For MyC = 1 To 3
- Brr(k, MyC) = Arr(MyR, MyC)
- Next MyC
- dic2(Arr(MyR, 3)) = ""
- End If
- Else
- dic1(Arr(MyR, 3)) = ""
- End If
- Next MyR
- Sheets("重复").Range("b2").Resize(k, 3) = Brr
- Application.ScreenUpdating = True
- MsgBox "用时:" & Format(Timer - T, "0.00秒")
- End Sub
复制代码 |
评分
-
查看全部评分
|