OzGrid

---

How to transpose single column into multiple columns and rows

 

Requirement:

 

The user has some data arranged in the following way:

 

The user would like it to be displayed as follows:

...

 

 

 

 

Solution:

 

Sub ertert()
Dim x, y(), i&, j&, k&
x = Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value
ReDim y(1 To UBound(x) / 6, 1 To 7)
For i = 1 To UBound(x) Step 6
    k = k + 1
    For j = 1 To 6
        y(k, j) = x(i + j - 1, 1)
    Next j
    y(k, 7) = x(i, 2)
Next i
Range("E1:K1").Resize(k).Value = y()
End Sub

 

Obtained from the OzGrid Help Forum.

Solution provided by nilem.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to create VBA to copy specific column from one sheet to another

How to use a macro or formula to copy data from cell to all cells in that group in adjacent column

How to compare two columns in excel, inserting blank rows moving associated data

How to run a macro if a column has a certain text in it

How to check if column header exists and if not then add column with that header

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

---

• 
• 
• 
• 
• 
•