|
Sub 一对多()
Dim Arr, i&, j&, drow1&, drow2&, sh As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Worksheets("Sheet1")
drow1 = .Range("a65536").End(3).Row
Arr = .Range("a2:b" & drow1)
End With
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
For i = 1 To drow1 - 1
If Not d1.exists(Arr(i, 1)) Then
d1(Arr(i, 1)) = Arr(i, 2)
Else
d1(Arr(i, 1)) = d1(Arr(i, 1)) & "/" & Arr(i, 2) '两个结果之间用/隔开
End If
Next
Set sh = Sheets.Add(After:=Sheets(Sheets.Count)) '在最后一张工作表后面添加一张工作表
Sheets(2).Select
Sheets(2).Name = "汇总完成"
Worksheets("汇总完成").UsedRange.ClearContents '将工作表内容清空
Worksheets("汇总完成").Cells.Select '选择工作表的所有单元格
Selection.NumberFormatLocal = "@" '将所有单元格设置成文本格式
Worksheets("Sheet1").Range("A1:B1").Copy _
Destination:=Worksheets("汇总完成").Range("A1") '复制源目标第一行标题并且粘贴到目标工作表的第一行
With Worksheets("汇总完成")
.[a2].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
.[b2].Resize(d1.Count, 1) = Application.Transpose(d1.items)
End With
Worksheets("汇总完成").UsedRange.EntireColumn.AutoFit '自动调整行高列宽
MsgBox "汇总完成"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
|
|