1

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

1 Answer 1

2

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.

3
  • If you share code, please explain what it does. Otherwise it will be of no help if the problem changes slightly.
    – Baarn
    Jul 28, 2012 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. Jul 28, 2012 at 12:38
  • Great its working...[:-bd] thanks a lot but i don understand what the code is doing?
    – Arusan
    Jul 30, 2012 at 4:34

You must log in to answer this question.

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