|
本帖最后由 林木水 于 2022-1-7 21:54 编辑
- Dim brr(1 To 10000, 1 To 1)
- Dim k As Integer
- Sub text()
- Dim d As New Dictionary
- Dim d1 As New Dictionary
- Dim d2 As New Dictionary
- Dim i As Integer
- For i = 2 To Range("a65536").End(xlUp).Row
- d(Cells(i, 1).Value) = d(Cells(i, 1).Value) & "、" & Cells(i, 2).Value
- If InStr(d(Cells(i, 1).Value), "、") = 1 Then d(Cells(i, 1).Value) = Mid(d(Cells(i, 1).Value), 2, Len(d(Cells(i, 1).Value)))
- Next i
- Range("c:z") = ""
- Range("d1").Resize(d.Count) = Application.Transpose(d.Keys)
- Range("c1").Resize(d.Count) = Application.Transpose(d.Items)
- Range("e1").Value = "对应情况"
- Range("f1").Value = "重复数值"
- Range("g1").Value = "重复个数"
- Erase brr
- Dim arr
- arr = d.Keys
- k = 0
- zuhe arr, 0, "", 0
- Range("e2").Resize(k) = brr
- Dim a, b 'a代表对应1,b代表对应2
- Dim arr1, arr2 '存2个人vs所有的数值
- Dim m As Integer, n As Integer '对照2个人数值用来循环,找出相同
- Dim k1 As Integer '存重复数字的个数
- ReDim crr(1 To k, 1 To 2)
- For i = 1 To k
- a = VBA.Split(brr(i, 1), "VS")(0)
- b = VBA.Split(brr(i, 1), "VS")(1)
- If InStr(d(a), "、") = 0 Then
- arr1 = Array(d(a))
- Else
- arr1 = VBA.Split(d(a), "、")
- End If
- If InStr(d(b), "、") = 0 Then
- arr2 = Array(d(b))
- Else
- arr2 = VBA.Split(d(b), "、")
- End If
- '..................去重
- For m = 0 To UBound(arr1)
- d1(arr1(m)) = arr1(m)
- Next m
- For n = 0 To UBound(arr2)
- d2(arr2(n)) = arr2(n)
- Next n
- arr1 = d1.Keys
- arr2 = d2.Keys
- '....................
- For m = 0 To UBound(arr1)
- For n = 0 To UBound(arr2)
- If arr1(m) = arr2(n) Then
- k1 = k1 + 1
- crr(i, 1) = crr(i, 1) & "、" & arr1(m)
- If InStr(crr(i, 1), "、") = 1 Then crr(i, 1) = Mid(crr(i, 1), 2, Len(crr(i, 1)) - 1)
- Exit For
- End If
- Next n
- Next m
- crr(i, 2) = k1
- k1 = 0
- Set d1 = Nothing: Set d2 = Nothing '重新加载字典去重
- Next i
- Range("f2").Resize(k, 2) = crr
- End Sub
- Sub zuhe(arr, x, sr, y)
- If y = 2 Then
- k = k + 1
- brr(k, 1) = Mid(sr, 3, Len(sr))
- Exit Sub
- End If
- If x <= UBound(arr) Then
- zuhe arr, x + 1, sr & "VS" & arr(x), y + 1
- zuhe arr, x + 1, sr, y
- End If
- End Sub
复制代码 思路:
1.去重名字
2.根据名字把所有数值重组成一串字符串
3.把每个人的数值拆分成一个数组,并去重
4.递归法把所有VS情况展示出来,递归法如果不会可以去针对性的去看下视频,这个方法抓取所有组合情况的时候非常好用
5.根据每个人的去重后的数值进行循环遍历比较,如果相同那么存入crr的第一列,并且以k1计数重复个数,最后存入crr第二列
6.最后输出结果crr
7.见附件
特别注意一点:
字典需要引用,不能用调用的,否则会出错,目前我还没找到原因。
|
|