Share [Social9_Share]

Transpose rows into separate columns

Transpose rows into separate columns

You would have faced this problem a ton of times, especially when you copy data from the web. Here is the problem..!

Imagine that you sourced this data and pasted into excel. Unfortunately in this data (Name, Address Line 1 & Line 2 and Phone) are all stacked up, one above the other in rows.

Transpose rows into separate columns 1

Assuming that each record has 4 rows and one empty row in between, how would you bring that in a columnar format? So that it looks something like this..

Transpose rows into separate columns 2

 

DOWNLOAD THE EXCEL FILE HERE

 

Post your answers in the comments

Post your solutions in the comments and if you have a file to share, please paste a link to download the file. I’ll soon update this post with 2 non vba based solutions with you

 

  • mma173

    Index (data, int(row()/5+1))*n + column offset)
    Where n is the record number
    and assuming the formula is starting from the first row.

  • GraH

    I smell a Power Query post here. Nice! Going to give a shot. BANG!

  • GraH

    With PQ (it is a classic Monkey job, right Chandeep?):
    1. Load data (I’ve named the range Data)
    2. Insert Index
    3. Insert Modulo 5
    4. Pivot other columns (starting from Modulo), do not aggregate
    5. Fill up columns 1,2,3
    6. Filter out NULL values from column 0
    7. delete columns you do not need
    8. rename the columns
    9. Load data

    M-code looks like this:
    let
    Source = Excel.CurrentWorkbook(),
    #”Filtered Rows” = Table.SelectRows(Source, each ([Name] = “Data”)),
    #”Removed Columns” = Table.RemoveColumns(#”Filtered Rows”,{“Name”}),
    Content = #”Removed Columns”{0}[Content],
    #”Changed Type” = Table.TransformColumnTypes(Content,{{“Column1″, type text}}),
    #”Added Index” = Table.AddIndexColumn(#”Changed Type”, “Index”, 0, 1),
    #”Inserted Modulo” = Table.AddColumn(#”Added Index”, “Inserted Modulo”, each Number.Mod([Index], 5), type number),
    #”Pivoted Column” = Table.Pivot(Table.TransformColumnTypes(#”Inserted Modulo”, {{“Inserted Modulo”, type text}}, “nl-BE”), List.Distinct(Table.TransformColumnTypes(#”Inserted Modulo”, {{“Inserted Modulo”, type text}}, “nl-BE”)[#”Inserted Modulo”]), “Inserted Modulo”, “Column1″),
    #”Filled Up” = Table.FillUp(#”Pivoted Column”,{“1”, “2”, “3”}),
    #”Filtered Rows1″ = Table.SelectRows(#”Filled Up”, each ([0] null)),
    #”Removed Columns1″ = Table.RemoveColumns(#”Filtered Rows1″,{“Index”, “4”}),
    #”Renamed Columns” = Table.RenameColumns(#”Removed Columns1″,{{“0”, “Name”}, {“1”, “Address1”}, {“2”, “Address2”}, {“3”, “Phone”}})
    in
    #”Renamed Columns”

  • PowerBINinja

    You can use Power Query for this. Load the range into PQ (“Get Data”). The trick is done with the “Pivot” functionality for which you need a couple of help rows. This is the code.

    let

    Origen = Excel.CurrentWorkbook(){[Name=”Tabla3″]}[Content],
    #”Tipo cambiado” = Table.TransformColumnTypes(Origen,{{“Columna1″, type text}}),
    #”Filas en blanco eliminadas” = Table.SelectRows(#”Tipo cambiado”, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {“”, null}))),
    #”Índice agregado” = Table.AddIndexColumn(#”Filas en blanco eliminadas”, “Índice”, 1, 1),
    #”Personalizada agregada” = Table.AddColumn(#”Índice agregado”, “Personalizado”, each Number.Mod([Índice]-1,4)),
    #”Personalizada agregada1″ = Table.AddColumn(#”Personalizada agregada”, “Personalizado.2″, each ([Índice]+3-[Personalizado])/4),
    #”Columnas quitadas” = Table.RemoveColumns(#”Personalizada agregada1″,{“Índice”}),
    #”Columna dinamizada” = Table.Pivot(Table.TransformColumnTypes(#”Columnas quitadas”, {{“Personalizado”, type text}}, “en-US”), List.Distinct(Table.TransformColumnTypes(#”Columnas quitadas”, {{“Personalizado”, type text}}, “en-US”)[Personalizado]), “Personalizado”, “Columna1″),
    #”Columnas quitadas1″ = Table.RemoveColumns(#”Columna dinamizada”,{“Personalizado.2″})

    in
    #”Columnas quitadas1”

    Att
    Power BI Ninja

  • Bernal de la Cruz

    First I made dummy row number, c4 is 4, c5 is c4+5…..
    Then used this formula for name=INDEX($B:$B,$C4+, Address Line1 =INDEX($B:$B,$C4+1), Address Line 2 = =INDEX($B:$B,$C4+2), Phone==INDEX($B:$B,$C4+3)
    First time commenting here…good day to all!

  • Ajay Kumar

    • Select Cells C4:F4
    • Use formula:- =transpose(b4:b7) and then use ctrl+sift+enter
    • Drag formula down
    • Copy all data and paste it by value
    • In a cell G4 use formula =COUNTIF(C4:F4,0) and drag it down
    • Remove all rows with greater than “0” count

  • Ajay Kumar

    • Select Cells C4:F4
    • Use formula:- =transpose(b4:b7) and then use ctrl+sift+enter
    • Drag formula down
    • Copy all data and paste it by value
    • In a cell G4 use formula =COUNTIF(C4:F4,0) and drag it down
    • Remove all rows with value greater than “0” in column G

  • Nishath
  • Glen Miller

    Okies, here is a noob attempt! With headers in row 3, I entered the following formula in cell D4: =OFFSET($B$3,(ROW(D1)-1)*4+COUNTA(D$3:D3)-1+COLUMN()-3,0) and copied across E4:G4 then copied down to row 103. Sure there’s an easier way, but it worked!

  • Ivan Bosnić

    1) Power Query:
    let
    Source = Excel.Workbook(File.Contents(“C:Transpose-rows-into-separate-columns.xlsx”), null, true),
    Sheet1_Sheet = Source{[Item=”Sheet1″,Kind=”Sheet”]}[Data],
    #”Changed Type” = Table.TransformColumnTypes(Sheet1_Sheet,{{“Column1”, type text}, {“Column2″, type text}}),
    #”Removed Columns” = Table.RemoveColumns(#”Changed Type”,{“Column2″}),
    #”Filtered Rows” = Table.SelectRows(#”Removed Columns”, each ([Column1] null)),
    #”Added Index” = Table.AddIndexColumn(#”Filtered Rows”, “Index”, 0, 1),
    #”Added Custom” = Table.AddColumn(#”Added Index”, “Mod”, each Number.Mod([Index], 4)),
    #”Added Custom1″ = Table.AddColumn(#”Added Custom”, “RoundDown”, each Number.RoundDown([Index] / 4 + 1)),
    #”Removed Columns1″ = Table.RemoveColumns(#”Added Custom1″,{“Index”}),
    #”Pivoted Column” = Table.Pivot(Table.TransformColumnTypes(#”Removed Columns1″, {{“Mod”, type text}}, “hr-HR”), List.Distinct(Table.TransformColumnTypes(#”Removed Columns1″, {{“Mod”, type text}}, “hr-HR”)[Mod]), “Mod”, “Column1″),
    #”Removed Columns2″ = Table.RemoveColumns(#”Pivoted Column”,{“RoundDown”}),
    #”Renamed Columns” = Table.RenameColumns(#”Removed Columns2″,{{“0”, “Name”}, {“1”, “Address Line 1”}, {“2”, “Address Line 2”}, {“3”, “Phone”}})
    in
    #”Renamed Columns”

    2) Formulas and filtering:
    do {=TRANSPOSE(B4:B7)} in 1st row (D4:G4)
    drag it all the way down (D5:G502)
    =MOD(ROW();5) in every row (H4:H502)
    filter column above, select only value 4 (every 5th row)
    c/p as values 🙂

    3) Notepad++ application (fastest way I know, cca 30 seconds):
    copy B4:B502 to Notepad++
    open Replace dialog (set Search Mode to Extended)
    replace rnrn with ###
    replace rn with t
    replace ### with rn
    c/p data to Excel

  • Ann

    The stacked data in a single column and repeats every 4 rows, no space row in between. =INDIRECT(ADDRESS((ROW($A1)-1)*4+COLUMN(B1),1)) pulled over 4 columns and down for as many rows needed.

  • Valeriy

    I used combination of INDIRECT formula, sign of concatenation & and ariphmetic progression.
    nth term of the sequence : a_{n}=a_{1}+(n-1)d},
    Formula for Name looks like that : INDIRECT(“B”&5+(C4-1)*5)

    I dont know how attach file, sorry….

  • Vaasu Bansal

    Steps 1. Give serial numbers in column 1.
    2.
    Then in side columns write 1,2,3,4 & 6,7,8,9. After this select this
    range & then drag it downwards & apply vlookup. https://uploads.disquscdn.com/images/63cc5348590c2289c825f4c9b0f33e8949b17a9ce477af5104984dcb4ab729fb.jpg

  • Seshagiri Thotakura

    Fill repetitive series 1 to 5 and apply filter as shown in figure.(https://uploads.disquscdn.com/images/c09a007f0309cb2a3988c25f2f178b9f6a9ad42a10e5475a5a550ca93835789e.jpg )
    and apply filter for each number paste in separate sheet as shown in figure.
    (https://uploads.disquscdn.com/images/08923013cbd3a77befd425efbd06a96645a291a1785f9d7f5b477137266ddd29.jpg )

  • Ajay Kumar

    · Select Cells C4:F4
    · Use formula:- =transpose(b4:b7) and then use ctrl+sift+enter
    · Drag formula down
    · Copy all data and paste it by value
    · In a cell G4 use formula =COUNTIF(C4:F4,0) and drag it down
    · Remove all rows with value greater than “0” in colmn G

Chandeep Chhabra

Popular Posts

© Goodly