INDEX函数：根据指定列转置指定行

公式：

第一步：在C1输入：=INDEX(A:A,SMALL(IF(MATCH($A$1:$A$10&"",$A$1:$A$10&"",0)=ROW($A$1:$A$10),ROW($A$1:$A$10),4^8),ROW(A1)))

注意：按CTRL+SHIFT+回车三键确定

第二步：在D1输入：=INDEX($B:$B,SMALL(IF($A$1:$A$10=$C1,ROW($A$1:$A$10),4^8),COLUMN(A1)))

注意：按CTRL+SHIFT+回车三键确定

---

【VBA】代码如下：

Sub test()

Dim r As Long, c As Long, i As Long, j As Integer, k As Integer

r = [a250000].End(3).Row

Range("e2:xfd" & r).ClearContents

k = Application.CountA(Range("d2:d" & r))

For j = 2 To k + 1

    For i = 2 To r

        If Cells(j, 4) = Cells(i, 1) Then

            c = Cells(j, 500).End(1).Column

            Cells(j, c + 1) = Cells(i, 2)

        End If

    Next i

Next j

End Sub