Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 23rd 05, 12:51 AM
Nickornyk
 
Posts: n/a
Default 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   Report Post  
Old March 23rd 05, 01:22 AM
Dave Peterson
 
Posts: n/a
Default

Always 3 cells per address and no gap between addresses???

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   Report Post  
Old March 23rd 05, 01:31 AM
Max
 
Posts: n/a
Default

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
instead in C1:

=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   Report Post  
Old March 23rd 05, 01:38 AM
Gord Dibben
 
Posts: n/a
Default

Nick

Are the addresses consistenly 3 rows per your example?

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   Report Post  
Old March 23rd 05, 01:55 AM
Alan Beban
 
Posts: n/a
Default

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?

If the functions in the freely downloadable file at
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   Report Post  
Old March 23rd 05, 12:54 PM
Lady Layla
 
Posts: n/a
Default

Download the free add-in Asap Utilities. They have a great Advanced transposer
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   Report Post  
Old March 23rd 05, 09:47 PM
Nickornyk
 
Posts: n/a
Default

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:

Always 3 cells per address and no gap between addresses???

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   Report Post  
Old March 23rd 05, 09:49 PM
Nickornyk
 
Posts: n/a
Default

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?

I have 10,000 address and my spreadsheet goes down to 67,000.

"Gord Dibben" wrote:

Nick

Are the addresses consistenly 3 rows per your example?

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   Report Post  
Old March 23rd 05, 10:54 PM
Gord Dibben
 
Posts: n/a
Default

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?

I have 10,000 address and my spreadsheet goes down to 67,000.

"Gord Dibben" wrote:

Nick

Are the addresses consistenly 3 rows per your example?

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?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I sort by row instead of by column? PercivalMound Excel Worksheet Functions 7 August 28th 06 10:41 PM
How do I reference every "n" cell in a column in Excel? Alma Excel Worksheet Functions 2 March 22nd 05 06:19 PM
Formula for a column comicfly Excel Discussion (Misc queries) 2 March 11th 05 12:16 PM
How do you sort a column of email addresses by domain name in Exc. Andy R Excel Discussion (Misc queries) 5 December 10th 04 09:26 PM
How can I sort an entire spreadsheet from a list prod sorter Excel Worksheet Functions 4 November 17th 04 03:43 AM


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

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017