1

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
2

3 Answers 3

4

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

3
  • what if we have another column whose rows are guaranteed to have the same value per group? i.e. same as the ID column, just another column. Can we transpose it into a column as well?
    – HeyJude
    Jun 8, 2022 at 19:47
  • @HeyJude Yes, you can. The methodology may be different. Suggest you pose a question with data, desired results, and show what you have tried and explain what went wrong. Jun 8, 2022 at 21:09
  • thanks, just did: here.
    – HeyJude
    Jun 8, 2022 at 22:31
1

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

will produce this in Sheet2:

enter image description here

0

http://www.excel-easy.com/examples/transpose.html

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .