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..

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
– Ron Rosenfeld, Commented Jan 25, 2020 at 0:23

Ron Rosenfeld · Accepted Answer · 2020-01-25 00:36:50Z

1

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"

answered Jan 25, 2020 at 0:36

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

waflfootyfacts Over a year ago

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.

Collectives™ on Stack Overflow

Transpose date in excel to include header row

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related