Split rows into columns

krishnanaik

Board Regular
Joined
Aug 5, 2005
Messages
81
Hi!!

Please help urgently !!

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

Name Dob Address Colour Name Dob Address Colour Name Dob Address Colour Name Dob Address Colour

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

Name Dob Address Colour
Name Dob Address Colour
Name Dob Address Colour
Name Dob Address Colour

Please can you provide a solution to this.

Many Thanks
Krishna
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
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
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Can you provide us with an idea of the work that you have done thus far in your own attempts to achieve your task?
 

krishnanaik

Board Regular
Joined
Aug 5, 2005
Messages
81
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
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

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

krishnanaik

Board Regular
Joined
Aug 5, 2005
Messages
81
i have no code. Its just raw data. There must be any easier way to split this without cutting and pasting the data.
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218

ADVERTISEMENT

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
:eek: :eek: :eek:
 

krishnanaik

Board Regular
Joined
Aug 5, 2005
Messages
81
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:

name add colour school
name add colour school
name add colour school
name add colour school
name add colour school

Does that make more sense?

Thanks
Krishna
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
krishnanaik

Your data seems to have changed from
Name, Dob, Address, colour
to
Name, Add, Colour, School
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
1Fred15/04/1985Address 1RedAnne08/08/1978Address 2BlueKen28/11/1980Address 3Pink
2
3Fred15/04/1985Address 1Red
4Anne08/08/1978Address 2Blue
5Ken28/11/1980Address 3Pink
6
Row to Columns
 

Forum statistics

Threads
1,136,595
Messages
5,676,707
Members
419,644
Latest member
KeelsM

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top