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  $90

Here's the final table I need:

ID  product1 product2 product3 product4
22       $10      $20      $30     
33                 $4       $5
44       $78                        $90

That can be accomplished by using a Pivot Table. With a single cell or the entire table selected, navigate to InsertTablesPivot 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:

enter image description here

| improve this answer | |


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.

| improve this answer | |

With data like this in Sheet1:

enter image description here

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")

    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 Sub

will produce this in Sheet2:

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.