10,000 addresses in column A; divided into 4 sections across.

[Guest]

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]

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.

 

[Max]

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

 

[Gord Dibben]

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

 

[Alan Beban]

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

 

[Lady Layla]

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



: 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?

 

[Guest]

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

 

[Guest]

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]

Nick

Select the column and F5>Special>Blanks and OK

With these blanks selected, right-click and Delete>Entire Row and OK.

BTW Excel has 65536 rows maximum.


Gord Dibben Excel MVP