How do I transfer repeating info in a column to 3 rows?

[Guest]

NOTE: I am not a programmer, and will thus need step by step instructions...

PROBLEM:
I have a column with three pieces of information that repeat in a pattern.
eg) Name, Phone Number, Email, (space), Name, Phone Number, Email, (space),
etc.

I know that I can transpose the multiple rows to columns, but that just
leaves me with a similar problem of repeating info in multiple columns!

Is there a formula to use to take all the similar info and list it in the
right columns?

Thanks.

 

[Mark Lincoln]

Jay, I'll assume your data begins in A1 with the first name, with a
phone number in A2, an email address in A3 and A4 is empty.

In B1 enter:

=A1

In C1 enter:

=A2

In D1 enter:

=A3

Click on B1 and drag to D4 to highlight the range. Then click on the
little black square at the bottom right of the highlighted area (the
bottom right of D4) and drag down to the row containing your last email
address. Click on Edit|Copy (or click the Copy button in the toolbar),
then click on Edit|Paste Special and in the dialog box under Paste,
click on Values, then click OK. You now have your data duplicated in
columns B through D. Leaving the area highlighted, click on the Sort
button to sort by column B. Then delete the data in column A and move
your data in columns B through D over if so desired. (Or you can
simply delete column A).

 

[Guest]

Here's a macro. Go to Tools, Macro, VB Editor. Then Insert, Module and
paste the following into it:

Sub ToColumns()
Dim x As Long
Dim y As Long
For y = 0 To Int(Selection.Rows.Count / 4 - 1)
For x = 0 To 3
ActiveCell.Offset(y, x + 2).Value = ActiveCell.Offset(x + 4 *
y).Value
Next x
Next y
End Sub

To run it, go back to Excel and select your info from the first name down to
the blank after the final email. Then click on Tools, Macro, Macros and
double-click ToColumns. Beware: this macro will overtype the columns to the
right of your list!

 

[Guest]

This will work for data in column A:

A1 contains the name
A2 contains the phone num ber
A3 contains the e-mail
A4 contains nothing
A5 contains the next name
..
..
..

In B1 enter
=INDIRECT("A" & ROW()*4-5+COLUMN())
copy from B1 to B2 thru B3
copy from B1 thru B3 down as far as you need

 

[Guest]

You are a victim of word wrap.

Sub ToColumns()
Dim x As Long
Dim y As Long
For y = 0 To Int(Selection.Rows.Count / 4 - 1)
For x = 0 To 3
ActiveCell.Offset(y, x + 2).Value = _
ActiveCell.Offset(x + 4 * y).Value
Next x
Next y
End Sub

 

[Guest]

I copied exactly, but came with an error due to:
(x + 4 *

y).Value

then put all on one line, but no result in right columns, still blank.

 

[Guest]

this worked fine, but the formula remained in every new column and is tied
to the original data - Origianl data cannot be removed or values will be zero.

I want to use this data in other ways, than visual representation in excel...

thanks

 

[Guest]

Tom, thanks! Perfect solution.
Have a great weekend!

J