Excel 2010 Transposing records with many rows into a single row

**Serenity** · 03-30-2012, 08:41 AM

Hi everyone,

Running Excel 2010:

I have to transpose a single column report with 11 fields one on top of the other for each record to a single row with a column for each field.

E.G.

From:
[Column1]
[Field 1]
[Field 2]
[Field 3]
[Field 4]

To:
[Column 1][Column 2][Column 3][Column 4]
[Row 1]

Any thoughts how this can be done, as the transpose only allows me to copy one record at a time?

Many thanks in advance.

**Dennis7849** · 04-05-2012, 08:08 PM

Copy:Paste Special>Transpose

It works on complete tables. I tested it in 2000 and 2007.

**Paul** · 04-05-2012, 08:24 PM

Hi Serenity, welcome to the forum.

If I understand correctly, you have a "record" in cells A1:A11, then a second "record" in A12:A22, a third in A23:A33, etc. down column A, and you want to transpose each of the 11 rows into one row using 11 columns. For example, A1:A11 would transpose into B1:L1. A12:A22 would transpose into B2:L2. So you end up with a table that is 11 columns wide and some amount of rows long?

If that's the case, try this macro. It organizes the data as I stated, and then deletes the original column A so you're only left with the final table.

Sub movedata()
Dim i As Long, j As Long, lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
j = 1

For i = 1 To lastrow Step 11
    Range("A" & i & ":A" & i + 10).Copy
    Range("B" & j).PasteSpecial Transpose:=True
    j = j + 1
Next i
Range("A:A").EntireColumn.Delete
End Sub