Transpose repeated, but uneven, sections of data in rows to columns

Currently, I have data in the form of two columns:

A 1
B 2
C 3
D 4
A 5
C 6
D 7
A 8
B 9
D 10
etc, in repeated sections such that the data in the first section (1 2 3 4) are together, the second section (5 6 7) are together and so forth.

I want to convert it into the form of
A B C D
1 2 3 4
5 6 7
8 9 10
Such that the data in each section are put into the same column (e.g. 1,5,8 under column A; 2,9 under column B; 3,6 under column C; 4,7,10 under column D)

Thanks!
central227

central227,

Welcome to the Excel Forum.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, click on the New Post button, then scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

'COLUMNS TO ROWS
Here's a macro for merging columns of data to one row matching for column A.

Columns to Rows - Part 2

There's a sample sheet you can test your data in. The only difference is that row 1 needs to "titles", so add those in A1 and B1, then your data in rows 2 down, then run the macro, it does exactly what you've shown.

Yes, your original data does not represent that each string of A-B-C-D are actually related. Much better to post real sample data like you did.

Here you, this will keep the related materials together, with data in columns A:B, run this:

Option Explicit

Sub Reformat()
Dim MyRNG As Range, cell As Range, NR As Long
Dim str1 As String, str2 As String, str3 As String, str4 As String

Set MyRNG = ActiveSheet.Range("A:A").SpecialCells(xlConstants)


Range("D1:G1").Value = [{"variableParameter","formula","meanValue","comment"}]
NR = 2

For Each cell In MyRNG
    Select Case cell.Value
        Case "variableParameter"
            If str1 <> "" Then
                Range("D" & NR) = str1
                Range("E" & NR) = str2
                Range("F" & NR) = str3
                Range("G" & NR) = str4
                NR = NR + 1
            End If
            str1 = cell.Offset(, 1).Value
            str2 = ""
            str3 = ""
            str4 = ""
            
        Case "formula"
            str2 = cell.Offset(, 1).Value
        Case "meanValue"
            str3 = cell.Offset(, 1).Value
        Case "comment"
            str4 = cell.Offset(, 1).Value
    End Select
Next cell

Range("D" & NR) = str1
Range("E" & NR) = str2
Range("F" & NR) = str3
Range("G" & NR) = str4
Range("D:G").Columns.AutoFit
End Sub

Please stop using the QUOTE button to quote our entire posts into your responses. It makes your thread very difficult to read. Just use the Quick Reply box below.

Definitely use the QUOTE button when you're discussing something specific someone has said, and be sure to edit the quote down to just the relevant sections.