Transpose Data from Rows to Columns

johndrew982 · Dec 7th 2021

The stock data I have comes in row format. I need a Macro to transpose it so it is in Column format. The data always starts at A3 and I take the next 11 rows. Then I transpose it to Sheet 2 starting on Row 2. Row 1 are the Headings. Then I need the macro to return to Sheet 1, add 13 to the last row number used and repeat: copy and transpose to the next row in Sheet 2. Continue until it reaches a blank in column A. Ozgrid Transpose Stock Data.xlsx

Mumps · Dec 7th 2021

Place the headings in row 1 of Sheet2 and try this macro:

Code

Sub TransposeData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = 3 To LastRow Step 13
        srcWS.Range("A" & x).Resize(12).Copy
        desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Transpose:=True
    Next x
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Display More

**alansidman** · Dec 7th 2021

An alternative solution that requires no coding is with Power Query

Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 13), type number),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Modulo", each ([Modulo] <> 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [Modulo]=1 then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Modulo] <> 1)),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows1", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows1", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Column1")
in
    #"Pivoted Column"

Display More

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Transpose Data from Rows to Columns

Participate now!

Share