Macro that transposes data (but looks at groups)

[Hopelessdreamz]

I have an EXTREMELY large data set in excel with varying data sets (some have 12 lines and some with 18, etc) that are currently in rows that needs to be transposed to columns. All the groupings are separated by a empty/blank line. I started the VBA to transpose this it but dont know how to include/look at the blank line and loop it to the end of each sheet. Any ideas/suggestions?

Data looks like something like this.

but i need it like this where it is stacked on top of each other:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Range("F1:F12").Select
Selection.Copy
Sheets("Sheet4").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet3").Select
Range("F14:F27").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet4").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("G14").Select</code>

 

[Eric W]

Welcome to the forum.

Try this in a copy of your workbook:

Sub TransposeIt()
Dim MyData As Variant, MyOutput() As Variant, r As Long, c As Long, i As Long
' https://www.mrexcel.com/forum/excel-questions/1085541-macro-transposes-data-but-looks-groups.html
' Eric W, 1/29/2019
    
' Read the data from Sheet3
    With Sheets("Sheet3")
        MyData = .Range("F1:F" & .Cells(Rows.Count, "F").End(xlUp).Row).Value
    End With
    
' Define an output array big enough to hold everything
    ReDim MyOutput(1 To UBound(MyData), 1 To 20)
    
' Set r(ow) to 1, and c(olumn) to 1
    r = 1
    c = 1
    
' Loop through the input data
    On Error GoTo Oops:
    For i = 1 To UBound(MyData)
        If MyData(i, 1) = "" Then           ' Empty cell?
            r = r + 1                       ' Go to next row
            c = 1                           ' Reset to column 1
        Else
            MyOutput(r, c) = MyData(i, 1)   ' Put the output to the right row/column
            c = c + 1                       ' Increment the column
        End If
    Next i
    
' Write all the output in one move
    Sheets("Sheet4").Range("A1").Resize(r, 20) = MyOutput
    Exit Sub
    
Oops:
    MsgBox "At least one group has over 20 items in it.  Increase the size of the MyOutput array and try again."
End Sub

 

[alansidman]

This is a task for Power Query. Look at this link for transposing a stacked column.

https://excelgorilla.com/power-bi/power-query/transform-stacked-data-into-table-using-modulo/

 

[sandy666]

as Alan said (post above) it can be done with PowerQuery

the same number of rows (13)

here is example with 4 rows only

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Custom.1[/td][td=bgcolor:#70AD47]Custom.2[/td][td=bgcolor:#70AD47]Custom.3[/td][td=bgcolor:#70AD47]Custom.4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]a[/td][td][/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]d[/td][/tr]

[tr=bgcolor:#FFFFFF][td]b[/td][td][/td][td]aa[/td][td]bb[/td][td]cc[/td][td]dd[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]c[/td][td][/td][td=bgcolor:#E2EFDA]aaa[/td][td=bgcolor:#E2EFDA]bbb[/td][td=bgcolor:#E2EFDA]ccc[/td][td=bgcolor:#E2EFDA]ddd[/td][/tr]

[tr=bgcolor:#FFFFFF][td]d[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]aa[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]bb[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]cc[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]dd[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]aaa[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]bbb[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ccc[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]ddd[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

example code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Column1")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Index", "Count"})
in
    #"Removed Columns"[/SIZE]