Split rows into columns

[krishnanaik]

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

 

[SteveO59L]

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]

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]

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]

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

 

[krishnanaik]

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

 

[Pekkavee]

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

 

[krishnanaik]

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]

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

=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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Fred	15/04/1985	Address 1	Red	Anne	08/08/1978	Address 2	Blue	Ken	28/11/1980	Address 3	Pink
2
3	Fred	15/04/1985	Address 1	Red
4	Anne	08/08/1978	Address 2	Blue
5	Ken	28/11/1980	Address 3	Pink
6
Row to Columns