Transposing columns to rows in Excel

Question

I would like to transpose my data in excel that currently looks like this:

and I want it to look like this:

You see the problem is that I'm not only need to transpose the columns with values, but also add row for each consecutive year for each indicator and for given country. Any help?

Answer 1

Not quite sure if you can do that based on your images, because of the size of your data. But with some filtering and rearranging, I think the TRANSPOSE(array) cell formula is what you are looking for:

---

Here is what we'll get:

---

• Select the destination range where you want to place your new/good data

• Enter =TRANSPOSE( into the formula bar

  

---

• Select the source range of your old/bad data (or type the address manually)

• Close the ) for the cell formula

  

• Hit Ctrl + Shift + Enter

NOTE: The TRANSPOSE function accepts an array as an input (this is done with Ctrl + Shift + Enter).

Answer 2

A Classical Transpose

After writing this it doesn't seem so classical anymore.

The Code

Sub ClassicalTranspose()

    Const cVntSource As Variant = "Sheet1"   ' Source Worksheet Name/Index
    Const cVntTarget As Variant = "Sheet2"   ' Target Worksheet Name/Index
    Const cStrSource As String = "A1:H14"    ' Source Range
    Const cStrTarget As String = "A1"        ' Target Cell Range
    Const cInt1 As Integer = 1               ' First Repeat Column
    Const cInt2 As Integer = 2               ' Last Repeat Column
    Const cStr1 As String = "Year"           ' First New Column Header
    Const cStr2 As String = "Value"          ' Second New Column Header

    Dim vntRep As Variant    ' Repeat Array
    Dim vntUni As Variant    ' Unique Array
    Dim vntTgt As Variant    ' Target Array

    Dim intRep1 As Integer   ' First Column of Repeat Range
    Dim intRep2 As Integer   ' Last Column of Repeat Range
    Dim intUni1 As Integer   ' First Column of Unique Range
    Dim intUni2 As Integer   ' Last Column of Unique Range
    Dim lngFirst As Long     ' First Row of Source Range
    Dim lngLast As Long      ' Last Row of Source Range

    Dim i As Long            ' Source Arrays Row Counter
    Dim j As Integer         ' Source Arrays Column Counter
    Dim k As Long            ' Target Array Row Counter
    Dim l As Integer         ' Unique Array Column Counter

    ' Paste Source Range into Source Arrays (Repeat and Unique Arrays).
    With ThisWorkbook.Worksheets(cVntSource).Range(cStrSource)
        intRep1 = .Column + cInt1 - 1
        intRep2 = .Column + cInt2 - 1
        intUni1 = .Column + cInt2
        intUni2 = .Columns.Count + .Column - 1
        lngFirst = .Row
        lngLast = .Rows.Count + .Row - 1
        With .Parent
            vntRep = .Range(.Cells(lngFirst, intRep1), .Cells(lngLast, intRep2))
            vntUni = .Range(.Cells(lngFirst, intUni1), .Cells(lngLast, intUni2))
        End With
    End With

    ' Resize Target Array.
    ReDim vntTgt(1 To (UBound(vntUni) - 1) * UBound(vntUni, 2) + 1, _
            1 To UBound(vntRep, 2) + 2)

    ' Write Repeat to Target Array
    For j = 1 To UBound(vntRep, 2)
        vntTgt(1, j) = vntRep(1, j)
    Next
    k = 1
    For l = 1 To UBound(vntUni, 2)
        For i = 2 To UBound(vntRep)
            k = k + 1
            For j = 1 To UBound(vntRep, 2)
                vntTgt(k, j) = vntRep(i, j)
            Next
        Next
    Next

    ' Write Unique to Target Array
    vntTgt(1, 1 + UBound(vntRep, 2)) = cStr1
    vntTgt(1, 1 + UBound(vntRep, 2) + 1) = cStr2
    k = 1
    For j = 1 To UBound(vntUni, 2)
        For i = 2 To UBound(vntUni)
            k = k + 1
            vntTgt(k, 1 + UBound(vntRep, 2)) = vntUni(1, j)
            vntTgt(k, 2 + UBound(vntRep, 2)) = vntUni(i, j)
        Next
    Next

    ' Paste Target Array into Target Range.
    With ThisWorkbook.Worksheets(cVntTarget).Range(cStrTarget)
        .Resize(UBound(vntTgt), UBound(vntTgt, 2)) = vntTgt
    End With

End Sub

Answer 3

Thanks very much for your answers. the best answer for my question was posted by Scott Craner with a link to another question already answered.

Ideal solution for my scenario was to use the Power Query (also known as Data Model in excel 2016 onwards), as I have very large data set which will not be possible to be handled by pivot tables due to the their size constraint. I used unpivot in the power query and it worked like a dream!

Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')