CODEWITHSUNDEEP
excelrows
dgorti
dgorti

Reputation: 1240

Convert groups of rows to a single row of columns

I have an Excel spreadsheet with repeating rows like this in a two column format:

ID   123
Name  xyx
Location dfff
custom sdfd

ID   124
Name  xyeeex
Location dfeeff
custom sdfdee

My data is consistent. Each data section contains 4 rows followed by 1 blank row. I ended up writing a macro in VB to get this done but I wonder if there is a more built-in way

I would like to get:

 
ID  Name Location Custom
123 ...
124 ...

So each group of rows need to get into one row. Even if I can get partially into some individual row format, I can apply further formulas to get to what I want But the real issue is how to get a repeating set of group of rows into a single row.

Upvotes: 3

Views: 8062

Answers (6)

Andrew Anderson
Andrew Anderson

Reputation: 1172

This question has a long history and I don't know if it's still helpful for someone. I was trying to solve the resembling problem with the only difference that ID is in the second column in the every respective row:

Location1  1
Name1      1
Address1   1
Location2  2
Name2      2
Address2   2

I ended up using Power Query in Excel. This video https://youtu.be/nJ7LzwiSwnw helped me to transform the original table to 2 columns with the data concatenated with a delimiter:

1 Location1;Name1;Address1
2 Location2;Name2;Address2

Splitting the second column is a trivia

Upvotes: 0

Allan F
Allan F

Reputation: 2288

Something like this ?

Sub trans5x()
    Set src = Sheets("sheet1") 'source data
    Set dst = Sheets("sheet2") 'dstination
    lr = src.Cells(Rows.Count, 1).End(xlUp).Row
    fr = 1 'first row of source data
    pr = 1 'first row of destination
    For i = fr To lr Step 5
        src.Cells(i, 1).Resize(5).Copy
        dst.Cells(pr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        pr = pr + 1
    Next
    dst.Range("A1:D1").EntireColumn.AutoFit 'resizes cols in destination sheet
End Sub

Upvotes: 0

NickG
NickG

Reputation: 9802

If you want to get this done quickly and simply, there is a plugin for Excel which allows you do this in seconds called Kutools for Excel (commercial [$39], but fully working free trial available).

Use the Transform Range function:

enter image description here

Source: https://www.extendoffice.com/documents/excel/3360-excel-transpose-every-5-rows.html

Upvotes: 0

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15551

Using method 1 of this general answer on how to transform among row, column and matrix ranges using only formulas, assuming your data starts in A1 (using columns A:B), and your target range is columns D:G, enter in D2 either

=INDEX($B$1:$B$100,(ROW()-ROW($D$2))*5+(COLUMN()-COLUMN($D$2)+1),1)

or

=OFFSET($B$1,(ROW()-ROW($D$2))*5+(COLUMN()-COLUMN($D$2)),0)

Copy down and to the left.

Upvotes: 3

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15551

I had already answered something similar here.

Assuming your data starts in A1 (using columns A:B), and your target range is columns D:G, enter

D2: =INDEX($B$1:$B$100,ROW()*5-9,1)
E2: =INDEX($B$1:$B$100,ROW()*5-8,1)
F2: =INDEX($B$1:$B$100,ROW()*5-7,1)
G2: =INDEX($B$1:$B$100,ROW()*5-6,1)

Copy down. You should be able to adapt this if relocating the source/target ranges.

Upvotes: 0

pnuts
pnuts

Reputation: 59440

Other than have somebody write code for you, the simplest way may be to copy your right-hand column into four columns and delete the top cell (shift up) from the first copy, the top two cells from the second copy and so on. Then filter on your left hand column, select and delete all rows other than those showing ID. Insert a row at the top and copy one set of row labels with Paste Special Transpose into the cell above you right-hand column. Then delete your first column.

Though using neither code nor formulae, this is viable for large data sets and, arguably, for moderate repetition.

Alternatively, copy RH column into Word as Unformatted text, Replace paired pilcrows with singles (^p^p with ^p), convert to 4 column Table and copy back in to Excel.

Upvotes: 0

Related Questions