EXCEL – 2D to flat

Input Data:-

Product VS Score	Resolution	Sound
HD TV	2	3
LCD TV	3	2

Expected output:-

Product	Category	Score
HD TV	Resolution	2
HD TV	Sound	3
LCD TV	Resolution	3
LCD TV	Sound	2

Create Excel macro and type below code. This will generate the expected output in Sheet2.

Sub Generate_Click()

    Dim oSht1, iSht1 As Worksheet
    Dim iRowNumber As Integer
    Dim iColNumber As Integer
    Dim iEndRowNumber As Integer
    Dim iEndColNumber As Integer
    Dim iOutputRowNumber As Double
    Dim i, j As Integer
    Set oSht1 = Sheet2
    Set iSht1 = Sheet1
    iRowNumber = 2
    iColNumber = 2
    iEndRowNumber = 3
    iEndColNumber = 3
    iOutputRowNumber = 2
    oSht1.Cells(1, 1).Value = "Product"
    oSht1.Cells(1, 2).Value = "Category"
    oSht1.Cells(1, 3).Value = "Score"
    For i = iColNumber To iEndColNumber
        For j = iRowNumber To iEndRowNumber
            oSht1.Cells(iOutputRowNumber, 1).Value = iSht1.Cells(j, 1).Value
            oSht1.Cells(iOutputRowNumber, 2).Value = iSht1.Cells(1, i).Value
            oSht1.Cells(iOutputRowNumber, 3).Value = iSht1.Cells(j, i).Value
             iOutputRowNumber = iOutputRowNumber + 1
        Next j
    Next i

End Sub