Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a sheet that looks like this:

   A   |   B   |   C   |   D   |   E   |
     62| Value1| Value2|       |       |
    345| Value3| Value4| Value5| Value6|
     17| Value7|       |       |       |
    111| Value8| Value9| ValueA|       |

I'd like to transform it to this:

   A   |   B   |
     62| Value1|
     62| Value2|
    345| Value3|
    345| Value4|
    345| Value5|
    345| Value6|
     17| Value7|
    111| Value8|
    111| Value9|
    111| ValueA|

This seems like a common enough procedure (I'm normalizing a table) that Excel would have this canned. Is there an easy way to do this, or does it get down to writing a macro or VBA?

share|improve this question
You can write simple formulas for this using the Excel functions hlookup or vlookup respectively – ernie Mar 22 '13 at 22:22
up vote 2 down vote accepted

Excel has a built in Transpose function that will switch row and columns but that won't work for your scenario. The only that I think will work is a VBA function. My VB is a little rusty but this one should work. You will need to modify the "A1:E5" range to cover the cells you want transform. And this copies the values from the first worksheet named "Sheet1" to the second named "Sheet2". If you have data in Sheet2 or have different named worksheets you will need to modify the code.

Sub Transform()

Dim rowStr As String
Dim rowIndex As Integer

rowIndex = 1

For Each Cell In Sheet1.Range("A1:E5")
    If Cell.Column = 1 Then
        rowStr = Cell.Value
    ElseIf Not IsEmpty(Cell.Value) Then
        Sheet2.Cells(rowIndex, 1) = rowStr
        Sheet2.Cells(rowIndex, 2) = Cell.Value
        rowIndex = rowIndex + 1
    End If
Next Cell

End Sub
share|improve this answer
Thanks Brad! I'll give this a try. – JW. Mar 24 '13 at 4:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .