Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 B1:D1 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
|
|||
|
|||
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 ... 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
|
|||
|
|||
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
|
|||
|
|||
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 B1:D10000 Alan Beban |
#6
|
|||
|
|||
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 ... : 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
|
|||
|
|||
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 B1:D1 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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
How do I reference every "n" cell in a column in Excel? | Excel Worksheet Functions | |||
Formula for a column | Excel Discussion (Misc queries) | |||
How do you sort a column of email addresses by domain name in Exc. | Excel Discussion (Misc queries) | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions |