My source table looks like this
ID Product Amount
22 product1 $10
22 product2 $20
22 product3 $30
33 product2 $4
33 product3 $5
44 product1 $78
44 product4 $90Here's the final table I need:
ID product1 product2 product3 product4
22 $10 $20 $30
33 $4 $5
44 $78 $903 Answers
That can be accomplished by using a Pivot Table. With a single cell or the entire table selected, navigate to Insert ► Tables ► Pivot Table
Then drag ID to the Rows area, Product to the Columns area, and Amount to the Values area. Play around with the formatting and labelling until you get what you want:
With data like this in Sheet1:
Running short macro:
Sub TwoDee() Dim s1 As Worksheet, s2 As Worksheet Dim N As Long, i As Long, v1 As String, v2 As String, v3 As Long Dim iRow As Long, iCol As Long Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") s2.Cells.Clear N = s1.Cells(Rows.Count, "A").End(xlUp).Row s1.Range("A2:B" & N).Copy s2.Range("A2") s2.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo s2.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo s2.Range("B2:B" & N).Copy s2.Range("B1").PasteSpecial Transpose:=True s2.Range("B2:B" & N).Clear For i = 2 To N v1 = s1.Cells(i, 1).Value v2 = s1.Cells(i, 2).Value v3 = s1.Cells(i, 3).Value iRow = s2.Range("A:A").Find(What:=v1, After:=s2.Range("A1")).Row iCol = s2.Range("1:1").Find(What:=v2, After:=s2.Range("A1")).Column s2.Cells(iRow, iCol) = v3 Next i
End Subwill produce this in Sheet2:
First hit on Google for excel 2010 transpose.
Basically, you select all the data you want to transpose, (including any column or row headers) Ctrl + C to copy it, right click on the top left cell where you want to paste it and paste special Check the transpose box and hit ok, and you're done.
2
