|
Sub ttt()
Dim arr(), nrow&, rng As Range
Dim dic As Object, dic2 As Object
Set dic = CreateObject("Scripting.Dictionary")
Set dic1 = CreateObject("Scripting.Dictionary")
With Sheet1
nrow = .Range("d1048576").End(xlUp).Row
arr = .Range("d3:d" & nrow).Value
'取重复性地址
For Each rng In .Range("d3:d" & nrow)
If dic(rng.Value) = "" Then
dic(rng.Value) = rng.Address
Else
dic(rng.Value) = dic(rng.Value) + "," + rng.Address
End If
Next
'前五个重复
For Each rng In .Range("d3:d" & nrow)
If dic1(Left(rng.Value, 5)) = "" Then
dic1(Left(rng.Value, 5)) = rng.Address
Else
dic1(Left(rng.Value, 5)) = dic1(Left(rng.Value, 5)) + "," + rng.Address
End If
Next
Dim addr1 As String, strtemp As String
Dim brr
'重复提示
For Each rng In .Range("d3:d" & nrow)
strtemp = ""
If InStr(dic(rng.Value), ",") > 1 Then
addr1 = rng.Address
brr = Split(dic(rng.Value), ",")
For Each i In brr
If i <> addr1 Then
strtemp = strtemp + Replace(i, "$", "") + ";"
End If
Next
.Range(Replace(addr1, "D", "I")) = "与" + strtemp + "重复"
End If
Next
Dim str1 As String
'前5位重复
For Each temp In dic1.keys()
If InStr(dic1(temp), ",") > 1 Then
str1 = Replace(dic1(temp), "D", "H")
.Range(str1) = "重复"
End If
Next
End With
End Sub
|
评分
-
1
查看全部评分
-
|