CODEWITHSUNDEEP
excelvba
J3FFK
J3FFK

Reputation: 684

Convert multi column table to single columns with repeat pattern

Suppose you have a column range (table) in Excel like this:

enter image description here

and need to convert it to a format like this:

enter image description here

I have searched for VBA script like here and there are several tools on the market, but cannot get it to work in desired output.

How can you do this with regular Excel formula or VBA script?

Upvotes: 0

Views: 381

Answers (1)

Error 1004
Error 1004

Reputation: 8220

Try:

Option Explicit

Sub test()

    Dim LC As Long, LR As Long, Number As Long, i As Long, j As Long, NR As Long
    Dim str As String

    'Everything included in "With Statement" refer to Sheet1
    With ThisWorkbook.Worksheets("Sheet1")

        'Find Last Column
        LC = .Cells(1, .Columns.Count).End(xlToLeft).Column

        'Find Las Row
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row

        'Loop from column two to last column
        For i = 2 To LC

            'Number get the value of row 1 and column i
            Number = .Cells(1, i).Value

            'Loop from row two to last row of each i
            For j = 2 To LR

                'str get the value of row j in column 1
                str = .Cells(j, 1).Value

                    NR = .Cells(.Rows.Count, "A").End(xlUp).Row

                    .Cells(NR + 1, 1).Value = Number
                    .Cells(NR + 1, 2).Value = str
                    .Cells(NR + 1, 3).Value = .Cells(j, i).Value


            Next j

        Next i

    End With

End Sub

Output:

enter image description here

Upvotes: 2

Related Questions