Remember Me?

#1
March 23rd 05, 12:51 AM
 Nickornyk Posts: n/a
10,000 addresses in column A; divided into 4 sections across.

I copied address off the internet. They are all isted in column A.

example:
First Place Awards
1234 Fifth Avenue
New York, NY 11111

How do I get row 2 to column b and row 3 to column c?

What I want.
A B C
First Place Awards 1234 Fifth Avenue New York, NY 11111

I have 10,000 address all in this one column that I'd like to have seperated
into 3 columns. I don't have the time to cut and paste.

Any way I can do this?

#2
March 23rd 05, 01:22 AM
 Dave Peterson Posts: n/a

If yes, put this in B1
=INDEX(\$A:\$A,MOD(COLUMN()-2,3)+1+(ROW()-1)*3)
and drag it to C1 and D1

Then drag B11 down until you run out of data.

Nickornyk wrote:

I copied address off the internet. They are all isted in column A.

example:
First Place Awards
1234 Fifth Avenue
New York, NY 11111

How do I get row 2 to column b and row 3 to column c?

What I want.
A B C
First Place Awards 1234 Fifth Avenue New York, NY 11111

I have 10,000 address all in this one column that I'd like to have seperated
into 3 columns. I don't have the time to cut and paste.

Any way I can do this?

--

Dave Peterson
#3
March 23rd 05, 01:31 AM
 Max Posts: n/a

One way to try ..

Assume data is in col A, A1 down
with one blank row in-between
groups of 3 lines each

First Place Awards
1234 Fifth Avenue
New York, NY 11111

2nd Place Awards
1235 Fifth Avenue
New York, NY 11111

Put in say, C1:

=OFFSET(\$A\$1,ROWS(\$A\$1:A1)*4-4+COLUMNS(\$A\$1:A1)-1,)

Copy C1 across to E1,
fill down until zeros appear, signalling exhaustion of data

Then copy cols C to E and paste special as values either in-situ or
elsewhere

If the data is w/o the single blank rows separating the groups of 3's, put

=OFFSET(\$A\$1,ROWS(\$A\$1:A1)*3-3+COLUMNS(\$A\$1:A1)-1,)

Then copy across to E1 and fill down as before
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Nickornyk" wrote in message
news
I copied address off the internet. They are all isted in column A.

example:
First Place Awards
1234 Fifth Avenue
New York, NY 11111

How do I get row 2 to column b and row 3 to column c?

What I want.
A B

C
First Place Awards 1234 Fifth Avenue New York, NY 11111

I have 10,000 address all in this one column that I'd like to have

seperated
into 3 columns. I don't have the time to cut and paste.

Any way I can do this?

#4
March 23rd 05, 01:38 AM
 Gord Dibben Posts: n/a

Nick

If one column and your data is consistently 3 rows, this macro will work.

Sub ColtoRows_NoError()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Application.ScreenUpdating = False
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Application.ScreenUpdating = True
End Sub

If more than one original column, post back with more details.

If unfamiliar with macros see David McRitchie's "getting started" site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP

On Tue, 22 Mar 2005 16:51:04 -0800, Nickornyk
wrote:

I copied address off the internet. They are all isted in column A.

example:
First Place Awards
1234 Fifth Avenue
New York, NY 11111

How do I get row 2 to column b and row 3 to column c?

What I want.
A B C
First Place Awards 1234 Fifth Avenue New York, NY 11111

I have 10,000 address all in this one column that I'd like to have seperated
into 3 columns. I don't have the time to cut and paste.

Any way I can do this?

#5
March 23rd 05, 01:55 AM
 Alan Beban Posts: n/a

Nickornyk wrote:
I copied address off the internet. They are all isted in column A.

example:
First Place Awards
1234 Fifth Avenue
New York, NY 11111

How do I get row 2 to column b and row 3 to column c?

What I want.
A B C
First Place Awards 1234 Fifth Avenue New York, NY 11111

I have 10,000 address all in this one column that I'd like to have seperated
into 3 columns. I don't have the time to cut and paste.

Any way I can do this?

http://home.pacbell.net/beban are available to your workbook, and if the
addresses are always 3lines, no gaps between them

=ArrayReshape(a1:a30000,10000,3) array entered into B110000

Alan Beban

#6
March 23rd 05, 12:54 PM

that you basically highlight the area of data, tell it how many columns and it
does it's job

www.asap-utilities.com

"Nickornyk" wrote in message
news : I copied address off the internet. They are all isted in column A.
:
: example:
: First Place Awards
: 1234 Fifth Avenue
: New York, NY 11111
:
: How do I get row 2 to column b and row 3 to column c?
:
: What I want.
: A B C
: First Place Awards 1234 Fifth Avenue New York, NY 11111
:
: I have 10,000 address all in this one column that I'd like to have seperated
: into 3 columns. I don't have the time to cut and paste.
:
: Any way I can do this?

#7
March 23rd 05, 09:47 PM
 Nickornyk Posts: n/a

Yes, lets say always 3 cells.

I pasted the formula into B1. But, when wrote drag to C1 and D1, I got
lost. Do you mean copy from B1 and paste across then down. Just not too
clear on it.

Thanks
Nickornyk

"Dave Peterson" wrote:

If yes, put this in B1
=INDEX(\$A:\$A,MOD(COLUMN()-2,3)+1+(ROW()-1)*3)
and drag it to C1 and D1

Then drag B11 down until you run out of data.

Nickornyk wrote:

I copied address off the internet. They are all isted in column A.

example:
First Place Awards
1234 Fifth Avenue
New York, NY 11111

How do I get row 2 to column b and row 3 to column c?

What I want.
A B C
First Place Awards 1234 Fifth Avenue New York, NY 11111

I have 10,000 address all in this one column that I'd like to have seperated
into 3 columns. I don't have the time to cut and paste.

Any way I can do this?

--

Dave Peterson

#8
March 23rd 05, 09:49 PM
 Nickornyk Posts: n/a

Your macro was amazing. It really helped me alot. Maybe you can help with
the spaces I have now.

I have various spaces throughout the spreadsheet. Not always the same
amount of spaces. How do I delete the empty rows so I can run your macro?

"Gord Dibben" wrote:

Nick

If one column and your data is consistently 3 rows, this macro will work.

Sub ColtoRows_NoError()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Application.ScreenUpdating = False
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Application.ScreenUpdating = True
End Sub

If more than one original column, post back with more details.

If unfamiliar with macros see David McRitchie's "getting started" site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP

On Tue, 22 Mar 2005 16:51:04 -0800, Nickornyk
wrote:

I copied address off the internet. They are all isted in column A.

example:
First Place Awards
1234 Fifth Avenue
New York, NY 11111

How do I get row 2 to column b and row 3 to column c?

What I want.
A B C
First Place Awards 1234 Fifth Avenue New York, NY 11111

I have 10,000 address all in this one column that I'd like to have seperated
into 3 columns. I don't have the time to cut and paste.

Any way I can do this?

#9
March 23rd 05, 10:54 PM
 Gord Dibben Posts: n/a

Nick

Select the column and F5SpecialBlanks and OK

With these blanks selected, right-click and DeleteEntire Row and OK.

BTW Excel has 65536 rows maximum.

Gord Dibben Excel MVP

On Wed, 23 Mar 2005 13:49:03 -0800, Nickornyk
wrote:

Your macro was amazing. It really helped me alot. Maybe you can help with
the spaces I have now.

I have various spaces throughout the spreadsheet. Not always the same
amount of spaces. How do I delete the empty rows so I can run your macro?

"Gord Dibben" wrote:

Nick

If one column and your data is consistently 3 rows, this macro will work.

Sub ColtoRows_NoError()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Application.ScreenUpdating = False
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Application.ScreenUpdating = True
End Sub

If more than one original column, post back with more details.

If unfamiliar with macros see David McRitchie's "getting started" site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP

On Tue, 22 Mar 2005 16:51:04 -0800, Nickornyk
wrote:

I copied address off the internet. They are all isted in column A.

example:
First Place Awards
1234 Fifth Avenue
New York, NY 11111

How do I get row 2 to column b and row 3 to column c?

What I want.
A B C
First Place Awards 1234 Fifth Avenue New York, NY 11111

I have 10,000 address all in this one column that I'd like to have seperated
into 3 columns. I don't have the time to cut and paste.

Any way I can do this?

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post PercivalMound Excel Worksheet Functions 7 August 28th 06 10:41 PM Alma Excel Worksheet Functions 2 March 22nd 05 06:19 PM comicfly Excel Discussion (Misc queries) 2 March 11th 05 12:16 PM Andy R Excel Discussion (Misc queries) 5 December 10th 04 09:26 PM prod sorter Excel Worksheet Functions 4 November 17th 04 03:43 AM

All times are GMT +1. The time now is 06:09 AM.