Transponha colunas do Excel desiguais para linhas [fechadas]

Question

Recebi uma tabela do Excel com um número diferente de itens por categoria:

Category A   Category B   Category C
22           11           1
34           15           6
55           4            18
33
36

Eu gostaria de transformar isso em:

Category     Item
Category A   22
Category A   34
Category A   55
Category A   33
Category A   36
Category B   11
Category B   15
[...]

O que é uma maneira rápida de conseguir isso?

    

Answer 1

Uma macro fará isso facilmente. A macro abaixo colocará o resultado em uma nova planilha.

Sub TransposeStuff()
Dim lLastRow As Long, lColLoop As Long, lLastCol As Long
Dim shtOrg As Worksheet, shtDest As Worksheet

'turn off updates to speed up code execution
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With


Set shtOrg = ActiveSheet
Set shtDest = Sheets.Add

shtDest.[a1] = "Category"
shtDest.[B1] = "Item"

lLastCol = shtOrg.Cells(1, Columns.Count).End(xlToLeft).Column


For lColLoop = 1 To lLastCol
    lLastRow = shtOrg.Cells(Rows.Count, lColLoop).End(xlUp).Row

    shtOrg.Range(shtOrg.Cells(2, lColLoop), shtOrg.Cells(lLastRow, lColLoop)).Copy
        shtDest.Cells(Rows.Count, 2).End(xlUp).Offset (1)

    shtDest.Range(shtDest.Cells(Rows.Count, 1).End(xlUp).Offset(1), _
                    shtDest.Cells(Rows.Count, 2).End(xlUp).Offset(, -1)).Value = shtOrg.Cells(1, lColLoop)

Next lColLoop

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub