excel中如何将重复列转化成行（如何用函数或是简单的vba实现）

Option Explicit

Sub 列转行()
Dim arr, dic As Object, i%
arr = Selection
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(arr)
If dic.exists(arr(i, 1)) Then
dic(arr(i, 1)) = dic.Item(arr(i, 1)) & "|" & arr(i, 2)
Else
dic(arr(i, 1)) = arr(i, 2)
End If
Next i

Range("C1").Resize(dic.Count, 1) = Application.Transpose(dic.keys)
Range("D1").Resize(dic.Count, 1) = Application.Transpose(dic.Items)
Range("D1").Resize(dic.Count, 1).TextToColumns Other:=True, OtherChar:="|"

Set dic = Nothing
End Sub

Sub 列转行交互版()
Dim arr, dic As Object, i%, RangA As Range
Set RangA = Application.InputBox(prompt:="请选择原始数据的区域", Type:=8)
arr = RangA
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(arr)
If dic.exists(arr(i, 1)) Then
dic(arr(i, 1)) = dic.Item(arr(i, 1)) & "|" & arr(i, 2)
Else
dic(arr(i, 1)) = arr(i, 2)
End If
Next i

Set RangA = Application.InputBox(prompt:="请选择目标数据的左上角。" & vbCrLf & "右下方数据将被覆盖！", Type:=8)
RangA.Resize(dic.Count, 1) = Application.Transpose(dic.keys)
RangA.Offset(0, 1).Resize(dic.Count, 1) = Application.Transpose(dic.Items)
Application.DisplayAlerts = False
RangA.Offset(0, 1).Resize(dic.Count, 1).TextToColumns Other:=True, OtherChar:="|"
Application.DisplayAlerts = True

Set dic = Nothing
End Sub

温馨提示：答案为网友推荐，仅供参考

当前网址：https://verywind.cn/ee/rxy372v72.html