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

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.

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

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).

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

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. Bewa this macro will overtype the columns to the
right of your list!


"Jay Goldblatt" wrote:

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.

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

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
--
Gary's Student


"Jay Goldblatt" wrote:

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.

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

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

--
Regards,
Tom Ogilvy

"Jay Goldblatt" wrote:

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.

"Martin" wrote:

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. Bewa this macro will overtype the columns to the
right of your list!


"Jay Goldblatt" wrote:

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.

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

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.

"Martin" wrote:

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. Bewa this macro will overtype the columns to the
right of your list!


"Jay Goldblatt" wrote:

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.

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

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

"Gary''s Student" wrote:

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
--
Gary's Student


"Jay Goldblatt" wrote:

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.

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

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

J

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Jay Goldblatt" wrote:

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.

"Martin" wrote:

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. Bewa this macro will overtype the columns to the
right of your list!


"Jay Goldblatt" wrote:

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.