Convert groups of rows to a single row of columns

Question

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.

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 — dgorti, Oct 24 '13 at 01:56
dgorti, Did you find anything posted useful? Please post feedback (a small action), for the benefit of the community. — sancho.s ReinstateMonicaCellio, Apr 20 '20 at 14:52

score 3 · Answer 1 · edited May 23 '17 at 12:06

3

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.

edited May 23 '17 at 12:06

Community

1
1

answered Jan 23 '15 at 10:52

sancho.s ReinstateMonicaCellio

14,708
20
93
185

score 0 · Answer 2 · answered Oct 24 '13 at 01:59

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.

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

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.

edited May 23 '17 at 12:12

Community

1
1

answered Jan 23 '15 at 06:23

sancho.s ReinstateMonicaCellio

14,708
20
93
185

score 0 · Answer 4 · answered Jul 17 '18 at 13:27

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:

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

score 0 · Answer 5 · answered Feb 13 '19 at 01:27

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

score 0 · Answer 6 · answered May 13 '20 at 20:06

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

Convert groups of rows to a single row of columns

6 Answers6