Splitting contents from multiple repeating cells in 1st column into 4columns

[daphtg]

In my first column I have 4 cells (1-4) (5-8) etc. with general
content.
The content from each of these 4 cells needs to be placed in a
separate
column to make a list that I can sort etc.
Example: COLUMN A
1 01-04-425-001
2 Heatherridge Road #301
3 Harry Smith
4 H25 Condo Fairfax Place
5 01-05-356-041
6 McGrath Street # 56
7 Mary Jones
8 B45 Condo Lemon Circle . . . . . . and so on and so on
every 4 cells.
I have hundreds of 4 cell descriptions. I just can't do this one by
one.
Can anyone help, PLEASE, PLEASE. I am really a novice at Excel though
I
use it for everything everyday.
There must be some kind of a formula or something.

Thank you so much in advance. I can't spend the next 6 months doing
this one
by one or retyping each one. I have many other things to do in my
job.

By the way I am on a MAC 10.4.11 using Excel 2004 if that makes a
difference.

 

[John McGhie]

Eeeewww..... You have now learned a very important reason WHY we say "never
mix more than one kind of data in a column It's hell's own job to split
it out if you do

If "Column A" was the only column in the spreadsheet, then we could perform
a transposition in Word:

Copy the whole of Column A, Paste it into Word, use Table>Convert>Table to
text to convert the thing into a long single list. Then use
Table>Convert>Text to Table but this time, specify the "Number of Columns"
as "4" and ignore the number of rows. Word will re-construct the data as a
four-column table, which you can paste pack into Excel as four columns.

Sadly, if you have more than one column of data in the original, that won't
work.

You can use a formula to transpose the data. You could use either the MOD
or Offset functions. I am using MOD.

1) Create five blank columns, one to the left of Column A, and four to the
right.

2) Place the number "1" in A1 and fill down to get 1 ~ 2,500

3) In Cell C1, enter the formula =IF(MOD(A1,4)=0,B1,"")

4) In Cell D1, enter the formula =IF(MOD(A1,4)=0,B2,"")

5) In Cell E1, enter the formula =IF(MOD(A1,4)=0,B3,"")

6) In Cell F1, enter the formula =IF(MOD(A1,4)=0,B4,"")

7) Select cells C1 through F1 and fill down, all the way to 2,500.

Each time Column A's number is evenly divisible by four, the formula will
transpose the contents of column B into that row.

You will need to extend this solution for each other column you have that
contains data: you must get all the data for each person into a single row,
otherwise when you sort, you will end up with "data salad" ‹ completely
useless.

Check carefully that there are no problems (e.g. A blank cell will not
matter so much, but if one data set contains five rows, you're screwed from
there down ...)

Now, select Columns C, D, E, and F and Copy. Document move the selection,
and Edit>Paste>Special and choose paste Values to replace the formulas with
their results. That makes the formulas hard text so it can't change again.

Hope this helps

In my first column I have 4 cells (1-4) (5-8) etc. with general
content.
The content from each of these 4 cells needs to be placed in a
separate
column to make a list that I can sort etc.
Example: COLUMN A
1 01-04-425-001
2 Heatherridge Road #301
3 Harry Smith
4 H25 Condo Fairfax Place
5 01-05-356-041
6 McGrath Street # 56
7 Mary Jones
8 B45 Condo Lemon Circle . . . . . . and so on and so on
every 4 cells.
I have hundreds of 4 cell descriptions. I just can't do this one by
one.
Can anyone help, PLEASE, PLEASE. I am really a novice at Excel though
I
use it for everything everyday.
There must be some kind of a formula or something.

Thank you so much in advance. I can't spend the next 6 months doing
this one
by one or retyping each one. I have many other things to do in my
job.

By the way I am on a MAC 10.4.11 using Excel 2004 if that makes a
difference.

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[daphtg]

Eeeewww.....  You have now learned a very important reason WHY we say "never
mix more than one kind of data in a column  It's hell's own job to split
it out if you do

If "Column A" was the only column in the spreadsheet, then we could perform
a transposition in Word:

Copy the whole of Column A, Paste it into Word, use Table>Convert>Table to
text to convert the thing into a long single list.  Then use
Table>Convert>Text to Table but this time, specify the "Number of Columns"
as "4" and ignore the number of rows.  Word will re-construct the data as a
four-column table, which you can paste pack into Excel as four columns.

Sadly, if you have more than one column of data in the original, that won't
work.

You can use a formula to transpose the data.  You could use either the MOD
or Offset functions.  I am using MOD.

1)  Create five blank columns, one to the left of Column A, and fourto the
right.

2)  Place the number "1" in A1 and fill down to get 1 ~ 2,500

3)  In Cell C1, enter the formula =IF(MOD(A1,4)=0,B1,"")

4)  In Cell D1, enter the formula =IF(MOD(A1,4)=0,B2,"")

5)  In Cell E1, enter the formula =IF(MOD(A1,4)=0,B3,"")

6)  In Cell F1, enter the formula =IF(MOD(A1,4)=0,B4,"")

7)  Select cells C1 through F1 and fill down, all the way to 2,500.

Each time Column A's number is evenly divisible by four, the formula will
transpose the contents of column B into that row.

You will need to extend this solution for each other column you have that
contains data: you must get all the data for each person into a single row,
otherwise when you sort, you will end up with "data salad" ‹ completely
useless.

Check carefully that there are no problems (e.g. A blank cell will not
matter so much, but if one data set contains five rows, you're screwed from
there down ...)

Now, select Columns C, D, E, and F and Copy.  Document move the selection,
and Edit>Paste>Special and choose paste Values to replace the formulas with
their results.  That makes the formulas hard text so it can't changeagain.

Hope this helps





 --

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

Thank you so much. Will try. Thank you. Thank you. Thank you. I
downloaded all these listings from one Township Assessors Office which
if far behind the times. I need the list desparately to do a mailing.

Thanks

Daphne

 

[John McGhie]

Hi Daphne:

OK, so go down there and hit them. They can't use it like that, so they
COULD give it to you in a proper format. And if you ask them whether you
should enquire at the Mayor's Office, they will!

Cheers

Thank you so much. Will try. Thank you. Thank you. Thank you. I
downloaded all these listings from one Township Assessors Office which
if far behind the times. I need the list desparately to do a mailing.

Thanks

Daphne

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]