Is there a way to transpose groups of columns to rows in excel?

Question

My source table in excel looks like this:

code    name1   perc1   name2   perc2   name3   perc3
11      x       10      x2      20      x3      70
12      y       20      y2      80        
13      z      100              
45      q      15       q2      85  

and here is the final table I need:

code    name1   perc1
11      x        10
11      x2       20
11      x3       70
12      y        20
12      y2       80 
13      z       100
45      q        15
45      q2       85

Answer 1

For example like so:

Formula in J2:

=IFERROR(INDEX($A$2:$A$5,SMALL(($B$2:$G$5<>"")*(ISEVEN(COLUMN($B$2:$G$5)))*(ROW($B$2:$G$5)-1),COUNTA($B$2:$G$5)-SUMPRODUCT((ISEVEN(COLUMN(B2:G5))))+(ROW()-1))),"")

Confirm through CtrlShiftEnter

Formula in K2:

=INDEX($A$2:$G$5,MATCH(J2,$A$2:$A$5,0),COUNTIF($J$2:J2,J2)*2)

Formula in L2:

=INDEX($A$2:$G$5,MATCH(J2,$A$2:$A$5,0),(COUNTIF($J$2:J2,J2)*2)+1)

Drag them down....

Answer 2

In order to keep this about transposing, I used a transposed array to build the target.

Option Explicit

Sub TransposeGroups()

    Dim i As Long, j As Long, arr1 As Variant
    Dim m As Long, n As Long, arr2 As Variant

    With Worksheets("sheet1")

        arr1 = .Cells(1, "A").CurrentRegion.Value2

        ReDim arr2(1 To 3, 1 To (UBound(arr1, 2) - 1) / 2 * (UBound(arr1, 1) - 1))

        m = 1
        For n = 1 To 3
            arr2(n, m) = arr1(m, n)
        Next n

        For i = 2 To UBound(arr1, 1)
            For j = 2 To UBound(arr1, 2) Step 2

                If arr1(i, j) = vbNullString Then Exit For

                m = m + 1
                arr2(1, m) = arr1(i, 1)
                arr2(2, m) = arr1(i, j)
                arr2(3, m) = arr1(i, j + 1)

            Next j
        Next i

        With .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Index))

            .Cells(1, "A").Resize(UBound(arr2, 2), UBound(arr2, 1)) = _
                Application.Transpose(arr2)

        End With

    End With
End Sub