Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 excel sheet as

A 1 2 3 4
B 1 2
C 1 2 3 4 5 

I need the output to be looked as

A 1
A 2
A 3
A 4
B 1
B 2
C 1
C 2
C 3
C 4
C 5

I will be greatful if anyone will help in finding a solution for this

share|improve this question
up vote 2 down vote accepted

You'll need to achieve this with VBA code.

Assuming you input sheet is called "input" and your output sheet is called "output" and that the input starts a cell A1 without headers, the following code will work:

Sub MakeOutput()

    Dim iInputRow As Long
    Dim iInputColumn As Long
    Dim iOutputRow As Long

    iOutputRow = 1 '- counter for which row to paste to
    '- loop through each row on the input sheet
    For iInputRow = 1 To Sheets("Input").Range("A" & Sheets("Input").Rows.Count).End(xlUp).Row
        '- loop through each column inside of each row
        For iInputColumn = 2 To Sheets("Input").Cells(iInputRow, 1).End(xlToRight).Column
            Sheets("Output").Range("A" & iOutputRow).Value = Sheets("Input").Range("A" & iInputRow).Value
            Sheets("Output").Range("B" & iOutputRow).Value = Sheets("Input").Cells(iInputRow, iInputColumn).Value
            iOutputRow = iOutputRow + 1
        Next iInputColumn
    Next iInputRow

End Sub

What this does is it loops through every row if your input data starting at A1 down to the last cell in A that has data.

Then in each row, it loops through every column that is populated.

For each of these columns of data in your input sheet it is copying the values of that pair to the output sheet.

share|improve this answer
If you share code, please explain what it does. Otherwise it will be of no help if the problem changes slightly. – Baarn Jul 28 '12 at 8:19
I usually just add a few comments inside of the code. But per your reccomendation I wrote out the process summary in English. – danielpiestrak Jul 28 '12 at 12:38
Great its working...[:-bd] thanks a lot but i don understand what the code is doing? – Arusan Jul 30 '12 at 4:34

Your Answer


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.