# Split rows into columns

#### krishnanaik

##### Board Regular
Hi!!

We have a row of data from row A-Z and need to split this into separate rows. ie.

These are along 1 row and we need them to be in seperate columns as follows:

Please can you provide a solution to this.

Many Thanks
Krishna

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
So,

the first occurrence of name is in Col A, the 2nd in Col E, the 3rd in Col I etc.

Thats a repeat of 4 cells, so your data cant go from A-Z. Please clarify

Can you provide us with an idea of the work that you have done thus far in your own attempts to achieve your task?

the data sequence runs from column A-K and then repeats across the row. we need to split this out into separate A-K rows down

Still don't see an example of the code you have written thus far and where you are stuck in your development.

i have no code. Its just raw data. There must be any easier way to split this without cutting and pasting the data.

Maybe you could use

Data - Text to Columns and use space to separate them
and then
Cut - Paste - and so on.

And you can record a macro on this, and then add a loop into it.

Pekka

This doesnt seem to work for data in rows only in columns. We have the data in rows. ie name add colour school name add colour school name add colour school name add colour school (repeated several times).

and we want it like:

Does that make more sense?

Thanks
Krishna

Hi Krishna

This solution assumes

- You have your raw data in sheet1 in A:T, starting in A1
- Since you want records of 4 cells this means that each row in your raw data table has 5 records/row (20 columns/4 cells per record). Each row in the raw data table will generate 5 rows in the result table.

In sheet2!A1

Code:
``=INDEX(Sheet1!\$A:\$T,1+INT((ROW()-ROW(\$A\$1))/5),1+4*MOD(ROW()-ROW(\$A\$1),5)+(COLUMN()-COLUMN(\$A\$1)))``

Copy across till D and down untill row 5 * (rows in the raw data table)

Hope this helps
PGC

krishnanaik

Your data seems to have changed from
to
or something similar.

However, see if this sort of structure is some use:

Formula in A3 (copied across and down) is:
=INDEX(\$1:\$1,1,4*(ROWS(A\$3:A3)-1)+1+MOD(COLUMNS(\$A3:A3)-1,4))
Mr Excel.xls
ABCDEFGHIJKLM
2
6
Row to Columns

Replies
5
Views
151
Replies
9
Views
243
Replies
3
Views
402
Replies
3
Views
116
Replies
0
Views
394

1,202,917
Messages
6,052,548
Members
444,591
Latest member
fauxlidae

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back