Convert Excel rows to columns

Question

How can I convert the rows in column A to columns and rows in column B to a single row?

Here is my current setup:

   ColumnA   ColumnB
  Question1  Answer1
  Question2  Answer2
  Question3  Answer3
  Question4  Answer4
  Question5  Answer5
  Question1  Answer6
  Question2  Answer7
  Question3  Answer8
  Question4  Answer9
  Question5  Answer10

What I would like is:

Question1 Question2 Question3 Question4 Question5
 Answer1   Answer2   Answer3   Answer4   Answer5
 Answer6   Answer7   Answer8   Answer9   Answer10

If there are not too many such groups, paste with transpose would be the easiest way... — tumchaaditya, Nov 08 '13 at 00:18
Unfortunately, there are about 400000 rows but yes I agree with minimal rows it would be easy to transpose them. — Tom, Nov 08 '13 at 03:20

Netloh · Accepted Answer · 2014-04-16T13:11:53.517

I think this is best solved with a VBA macro. If you got your current table in Sheet1 and want to do your conversion to Sheet2 then the following macro will do the conversion.

Option Explicit
Sub ConvertRows()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i As Integer, j As Integer
    Set ws1 = ActiveWorkbook.Sheets("Sheet1")
    Set ws2 = ActiveWorkbook.Sheets("Sheet2")

    'Lists each value from column 1
    For i = 1 To ws1.Cells(Rows.Count, 1).End(xlUp).Row Step 1
        For j = 1 To ws2.Cells(1, Columns.Count).End(xlToLeft).Column + 1 Step 1
            If ws2.Cells(1, j).Value = ws1.Cells(i, 1).Value Then Exit For
            If ws2.Cells(1, j).Value = vbNullString Then
                ws2.Cells(1, j).Value = ws1.Cells(i, 1).Value
                Exit For
            End If
        Next j
    Next i

    'Fills columns with matching values
    For i = 1 To ws1.Cells(Rows.Count, 1).End(xlUp).Row Step 1
        For j = 1 To ws2.Cells(1, Columns.Count).End(xlToLeft).Column Step 1
            If ws2.Cells(1, j).Value = ws1.Cells(i, 1).Value Then
                ws2.Cells(Rows.Count, j).End(xlUp).Offset(1, 0).Value = ws1.Cells(i, 2).Value
            End If
        Next j
    Next i
End Sub

score 0 · Answer 2 · answered Nov 08 '13 at 00:04

The NON-VBA method:

If you are dealing with a one-off problem, a simple way to handle conversions from columns to rows is by using the INDEX() function. I would suggest using something along the lines of the following in the first destination cells (for where you want "Question1" with "Question2" below it to be placed):

Question1 Destination Cell:
=INDEX($A:$A,COLUMN(A1))

Answer1 Destination Cell:
=INDEX($B:$B,COLUMN(A1))

From there, just copy the equations to the right until you have spanned as many columns as you have rows as this allows your column to row conversion to expand indefinitely to the right.

**This will work as long as your Questions & Answers data is in ColumnA and ColumnB as you specified but can be modified accordingly (using an alternate method for COLUMN(A1) to locate the row you are moving).

AND, if you want your questions and answers to stack after completing a set of 5 columns as you show above, set it up like this:

Question1 Destination Cell:
=INDEX($A:$A,COLUMN(A1))

Answer1 Destination Cell:
=INDEX($B:$B,COLUMN(A1))

Answer6 Destination Cell:
=INDEX($B:$B,MATCH([non-locked cell address of Answer1],$B:$B,0)+5)

From there, copy those three cells over to span 5 columns to the right. AND THEN, to expand downward, only copy the row of calculations that starts with Answer6 and paste those down the sheet to cover however many rows you have in ColumnA and ColumnB.

This method will produce this result:

Question1 Question2 Question3 Question4 Question5
 Answer1   Answer2   Answer3   Answer4   Answer5
 Answer6   Answer7   Answer8   Answer9   Answer10

Cheers.

Convert Excel rows to columns

2 Answers2