Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.