Rearrange Data from rows to columns

[NCase]

Hello Everyone

I need to rearrange a dataset from rows to columns in sheet 1 of attached excel spreadsheet. The data repeats every 13 rows. I made an example of what I am trying to achieve in sheet 2. Can anyone suggest a way to do this?

Best,

Francis

 

[Marc L]

Hi !​

​

As a beginner starter :​

Sub Demo()
        Dim C&, L&, N%, R&, V
    With Sheets(1).[A1].CurrentRegion.Rows
            C = .Range("A2").End(xlDown).Row - 2
            If .Count Mod C <> 1 Then Beep: Exit Sub
            N = .Columns.Count - 2
            R = 2
            V = Application.Transpose(.Cells(3).Resize(, N).Value)
            Sheets(2).UsedRange.Clear
            Application.ScreenUpdating = False
            Sheets(2).[A1].Value = .Cells(1).Value
            Sheets(2).[B1].Resize(, C + 1).Value = Application.Transpose(.Cells(2).Resize(C + 1).Value)
        For L = 2 To .Count + 1 - C Step C
            Sheets(2).Cells(R, 1).Resize(N).Value = .Cells(L, 1).Value
            Sheets(2).Cells(R, 2).Resize(N).Value = V
            Sheets(2).Cells(R, 3).Resize(N, C).Value = Application.Transpose(.Cells(L, 3).Resize(C, N).Value)
            R = R + N
        Next
    End With
            Sheets(2).UsedRange.HorizontalAlignment = xlCenter
            Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[p45cal]

You can also try with a pivot table, see Sheet1 of the attached, where I've copy/pasted values from the pivot to column Q and did find & replace on column R to get rid of the "Sum of " strings.