How to transpose single column into multiple columns and rows
Category: [Excel] Demo Available
How to transpose single column into multiple columns and rows
Requirement:
The user has some data arranged in the following way:
| Spain | Europe |
| Constitutional Monarchy | |
| Spanish | |
| Euro | |
| GMT+1 | |
| www.espana.es | |
| France | Europe |
| Republic | |
| French | |
| Euro | |
| GMT+1 | |
| www.france.fr | |
| Germany | Europe |
| Republic | |
| German | |
| Euro | |
| GMT+1 | |
| www.deutschland.de | |
| Italy | Europe |
| Republic | |
| Italian | |
| Euro | |
| GMT+1 | |
| www.italia.it |
The user would like it to be displayed as follows:
| Spain | Constitutional Monarchy | Spanish | Euro | GMT+1 | www.espana.es | Europe |
| France | Republic | French | Euro | GMT+1 | www.france.fr | Europe |
| Germany | Republic | German | Euro | GMT+1 | www.deutschland.de | Europe |
| Italy | Republic | Italian | Euro | GMT+1 | www.italia.it | Europe |
| Morocco | Constitutional Monarchy | French,Arabic | Dinar | GMT+1 | www.maroc.ma | Africa |
| ... |
...
Solution:
Code:
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.