Convert multiple rows into single column based on values

Question

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

Answer 1

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.