Transpose date in excel to include header row

Question

I have data as below which records players name and number of goals kicked per round. Blanks mean they didnt play the x means they played but didnt kick a goal.

Surname | First | Second | R1 | R2 | R3 | R4 | R5|
Smith   | Barry | John   | x  |    |    |    | 2 |
Jones   | Murry | Fred   | x  |  3 |  2 |  1 | 2 |
Wills   | Geoff | Mike   | x  |    |    |  x | x |

And Need it to display like:

Smith | Barry | John | R1 | x |
Smith | Barry | John | R5 | 2 |
Jones | Murry | Fred | R1 | x |
Jones | Murry | Fred | R2 | 3 |
Jones | Murry | Fred | R3 | 2 |
Jones | Murry | Fred | R4 | 1 |
Jones | Murry | Fred | R5 | 2 |
Wills | Geoff | Mike | R1 | x |
Wills | Geoff | Mike | R4 | x |
Wills | Geoff | Mike | R5 | x |

So far I have this code in VBA

Sub NewLayout()
    For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
        For j = 0 To 4
        If Cells(i, 4 + j) <> vbNullString Then
            intCount = intCount + 1
            Cells(i, 1).Copy Destination:=Cells(intCount, 10)
            Cells(i, 2).Copy Destination:=Cells(intCount, 11)
            Cells(i, 3).Copy Destination:=Cells(intCount, 12)
            Cells(i, 4 + j).Copy Destination:=Cells(intCount, 13)
        End If
        Next j
    Next i
End Sub

Which is getting me most of what I need without returning the header row ie R1, R2, R3 etc..

see second answer: https://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal — Scott Craner, Jan 24 '20 at 23:54
Use Power Query. Select the first three columns, then **unpivot other columns** *(assuming many of your "value" entries in your results table are erroneous. If not you need to explain the logic behind them)* — Ron Rosenfeld, Jan 25 '20 at 00:23

score 1 · Accepted Answer · answered Jan 25 '20 at 00:36

To modify your code, you can change your last copy line slightly, and add a line to copy the relevant header row

        Cells(i, 1).Copy Destination:=Cells(intcount, 10)
        Cells(i, 2).Copy Destination:=Cells(intcount, 11)
        Cells(i, 3).Copy Destination:=Cells(intcount, 12)
        Cells(1, 4 + j).Copy Destination:=Cells(intcount, 13)
        Cells(i, 4 + j).Copy Destination:=Cells(intcount, 14)

But I would suggest using Power Query, where all you have to do is

select the first three columns
Unpivot other columns

That can be done from the UI but here is the M-Code

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Surname", "First", "Second"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Thanks Ron - I've just added the extra row into the VBA which has worked a treat as I am not familiar with Power Query. — waflfootyfacts, Jan 25 '20 at 00:44

Transpose date in excel to include header row

1 Answers1