How to transpose a columns into lists with subsets

[andrea1982]

Does anyone knows how could I transpose 32,000 rows and stack several columns (same category, employees) in one column in the following way?

[TABLE="width: 435"]
<tbody>[TR]
[TD="class: xl63, width: 87, align: center"]Company[/TD]
[TD="class: xl63, width: 87, align: center"]Employee[/TD]
[TD="class: xl63, width: 87, align: center"]Employee[/TD]
[TD="class: xl63, width: 87, align: center"]Employee[/TD]
[TD="class: xl63, width: 87, align: center"]Employee[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]1[/TD]
[TD="class: xl64, align: center"]A[/TD]
[TD="class: xl64, align: center"]B[/TD]
[TD="class: xl64, align: center"]C[/TD]
[TD="class: xl64, align: center"]D[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]2[/TD]
[TD="class: xl64, align: center"]D[/TD]
[TD="class: xl64, align: center"]A[/TD]
[TD="class: xl64, align: center"][/TD]
[TD="class: xl64, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]3[/TD]
[TD="class: xl64, align: center"]E[/TD]
[TD="class: xl64, align: center"]F[/TD]
[TD="class: xl64, align: center"]A[/TD]
[TD="class: xl64, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]…[/TD]
[TD="class: xl64, align: center"]…[/TD]
[TD="class: xl64, align: center"]…[/TD]
[TD="class: xl64, align: center"]…[/TD]
[TD="class: xl64, align: center"]…[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]32,000[/TD]
[TD="class: xl64, align: center"]F[/TD]
[TD="class: xl64, align: center"][/TD]
[TD="class: xl64, align: center"][/TD]
[TD="class: xl64, align: center"][/TD]
[/TR]
</tbody>[/TABLE]


to

[TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl63, width: 87, align: center"]Company[/TD]
[TD="class: xl63, width: 87, align: center"]Employee[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"]A[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"]B[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"]C[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"]D[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]2[/TD]
[TD="class: xl63, align: center"]D[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]2[/TD]
[TD="class: xl63, align: center"]A[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]3[/TD]
[TD="class: xl63, align: center"]E[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]3[/TD]
[TD="class: xl63, align: center"]F[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]3[/TD]
[TD="class: xl63, align: center"]A[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]…[/TD]
[TD="class: xl64, align: center"]…[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]32000[/TD]
[TD="class: xl64, align: center"]F[/TD]
[/TR]
</tbody>[/TABLE]
<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {border:.5pt solid windowtext;}.xl64 {text-align:center; border:.5pt solid windowtext;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {text-align:center; border:.5pt solid windowtext;}.xl64 {color:black; text-align:center; border:.5pt solid windowtext;}--></style>

 

[DRSteele]

Welcome to the forum.

I think you can use PowerQuery to Unpivot the data.

These videos on the ExcelIsFun channel on youtube might illuminate things for you.

https://www.youtube.com/watch?v=JfuFGMfgvfY
https://www.youtube.com/watch?v=YuoEegbrW1I
https://www.youtube.com/watch?v=0WBgD0l5r-4

 

[tonyyy]

andrea1982,

Welcome to the Board.

If you're comfortable with a vba approach, you might consider the following...

Sub ResizeEmployees()
Application.ScreenUpdating = False
Dim LastRow1 As Long, LastRow2 As Long, LastColumn As Long, i As Long, j As Long, k As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets(1)
Set ws2 = Sheets.Add(after:=ws1)
LastRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
ws2.Range("A1:B1").Value = ws1.Range("A1:B1").Value
For i = 2 To LastRow1
    LastColumn = ws1.Cells(i, Columns.Count).End(xlToLeft).Column
    LastRow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    ws1.Cells(i, 1).Copy
    ws2.Cells(LastRow2, 1).Resize((LastColumn - 1), 1).Insert
    k = 0
    For j = 2 To LastColumn
        ws2.Cells(LastRow2 + k, 2).Value = ws1.Cells(i, j).Value
        k = k + 1
    Next j
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

The code adds a worksheet to accommodate the transposed data.

Cheers,

tonyyy