|
- Sub test()
- Dim DicA As Object, DicB As Object, d As Variant
- Dim arrA As Variant, arrB As Variant, arrTgt As Variant, arrTmp As Variant
- Dim iNum As Integer, i As Integer
-
- '读取所需字段
- '假设工作表名称是:Sheet1
- '假设读取数据在: A 列与 C 列
- '请自行根据实际情况修改
- With Sheets("Sheet1")
- '取数据行数
- iNum = .Cells(Rows.Count, "A").End(xlUp).Row - 1
- arrA = .Range("A2").Resize(iNum, 1)
- arrB = .Range("C2").Resize(iNum, 1)
- End With
-
- '构建两个字典
- Set DicA = CreateObject("Scripting.Dictionary")
- Set DicB = CreateObject("Scripting.Dictionary")
- For i = 1 To iNum
- DicA(arrA(i, 1)) = 0
- DicB(arrA(i, 1) & "-" & arrB(i, 1)) = arrA(i, 1)
- Next
-
- '统计,A 列同一个项在 C 列有几个不同的值
- For Each d In DicB
- DicA(DicB(d)) = DicA(DicB(d)) + 1
- Next
-
- '去掉不重复的项,留下数量在 2 个以上的项
- For Each d In DicA
- If DicA(d) = 1 Then DicA.Remove (d)
- Next
-
- '输出结果
- With Sheets("Sheet1")
- .Range("H2").Resize(DicA.Count, 1) = Application.WorksheetFunction.Transpose(DicA.Keys)
- .Range("I2").Resize(DicA.Count, 1) = Application.WorksheetFunction.Transpose(DicA.items)
- End With
-
- '收尾
- Set DicA = Nothing
- Set DicB = Nothing
- End Sub
复制代码
好像你第一次提问和第二次提问的内容又不同了,我还是按照 A 、C 列做了演示,只要 A 相同而 C 不同的都列出来。
你自己修改吧。 |
|