3

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?

1
  • You can write simple formulas for this using the Excel functions hlookup or vlookup respectively
    – ernie
    Mar 22, 2013 at 22:22

1 Answer 1

3

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
0

You must log in to answer this question.

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