I have 37, 800 rows and 1 column. Column A is as follows:
A1 George Brown
A2 1234 Street
A3 Browntown, USA 00234
A4 blank
A5 blank
A6 blank
A7 Betty Smith
A8 2345 Avenue
A9 Thistown, USA 00987
And the pattern repeats for the next 37,800 rows. I need to get 3 columns
setup.
A1: Name
B1: Address
C1: City, State Zip
I have a little knowledge of VBA but on this one I don't know where to start.
Any suggestions would be very appreciated.

If you are only a little familar with VBA, I recommend not using it for this
example since you cannot undo it (although you can save multiple versions).

In B1:B6 place a 1. In cells C1:C6 place a 1,2,3...6.
In B7 place the following formula "=B1+1".
In C7 place the following formula "=C1".
Drag both of these cells down to the end of your document.
Sort A1:C37800 by column C, then by column B.
Delete the last half (rows 18901:37800- this deletes anything with a 4,5,6
in column C- since this is all blank).

Now copy cells A1:A18900 (this should now be the last cell with data in it).
Paste it in cells D1, E2, and F3 (it will look like a staircase).
Delete rows 18901,18902.
Sort A1:F18900 by column C, then B.
Delete the last two thirds (rows 6301:18900).
What is left should be exactly what you are looking for. You can then
delete columns A,B,C to move D,E,F into the position that you want.


"Northwoods" wrote:

I have 37, 800 rows and 1 column. Column A is as follows:
A1 George Brown
A2 1234 Street
A3 Browntown, USA 00234
A4 blank
A5 blank
A6 blank
A7 Betty Smith
A8 2345 Avenue
A9 Thistown, USA 00987
And the pattern repeats for the next 37,800 rows. I need to get 3 columns
setup.
A1: Name
B1: Address
C1: City, State Zip
I have a little knowledge of VBA but on this one I don't know where to start.
Any suggestions would be very appreciated.

Oops. The instructions I gave you will give you blanks in the first record's
address and city, state, zip fields. Just copy and paste them in prior to
deleting columns A:C at the end.

"JVL_DarkHorse" wrote:

If you are only a little familar with VBA, I recommend not using it for this
example since you cannot undo it (although you can save multiple versions).

In B1:B6 place a 1. In cells C1:C6 place a 1,2,3...6.
In B7 place the following formula "=B1+1".
In C7 place the following formula "=C1".
Drag both of these cells down to the end of your document.
Sort A1:C37800 by column C, then by column B.
Delete the last half (rows 18901:37800- this deletes anything with a 4,5,6
in column C- since this is all blank).

Now copy cells A1:A18900 (this should now be the last cell with data in it).
Paste it in cells D1, E2, and F3 (it will look like a staircase).
Delete rows 18901,18902.
Sort A1:F18900 by column C, then B.
Delete the last two thirds (rows 6301:18900).
What is left should be exactly what you are looking for. You can then
delete columns A,B,C to move D,E,F into the position that you want.


"Northwoods" wrote:

I have 37, 800 rows and 1 column. Column A is as follows:
A1 George Brown
A2 1234 Street
A3 Browntown, USA 00234
A4 blank
A5 blank
A6 blank
A7 Betty Smith
A8 2345 Avenue
A9 Thistown, USA 00987
And the pattern repeats for the next 37,800 rows. I need to get 3 columns
setup.
A1: Name
B1: Address
C1: City, State Zip
I have a little knowledge of VBA but on this one I don't know where to start.
Any suggestions would be very appreciated.

U¿ytkownik "Northwoods" napisa³ w
wiadomo¶ci ...
I have 37, 800 rows and 1 column. Column A is as follows:
A1 George Brown
A2 1234 Street
A3 Browntown, USA 00234
A4 blank
A5 blank
A6 blank
A7 Betty Smith
A8 2345 Avenue
A9 Thistown, USA 00987
And the pattern repeats for the next 37,800 rows. I need to get 3 columns
setup.
A1: Name
B1: Address
C1: City, State Zip
I have a little knowledge of VBA but on this one I don't know where to
start.
Any suggestions would be very appreciated.

in b2 insert formula
=INDIRECT(ADDRESS(ROW(R[-1]C[-1])*6+1,1))
c2
=INDIRECT(ADDRESS(ROW(R[-1]C[-1])*6+2,1))
d2
=INDIRECT(ADDRESS(ROW(R[-1]C[-1])*6+3,1))
and copy them down as u get all addresses
mcg
mcg

Try this and then you can just delete column A it has finished and you should
have name in column A, address in column B, and city, state, zip in column C.

Sub Jeff()
Dim Name As String
Dim Address As String
Dim CSZ As String
Dim row As Long
Dim row1 As Long

row = 1
row1 = 1

Name = ActiveSheet.Cells(row, 1).Value
Address = ActiveSheet.Cells(row + 1, 1).Value
CSZ = ActiveSheet.Cells(row + 2, 1)

Do While Name < ""
ActiveSheet.Cells(row1, 2).Value = Name
ActiveSheet.Cells(row1, 3).Value = Address
ActiveSheet.Cells(row1, 4).Value = CSZ

row = row + 6
row1 = row1 + 1

Name = ActiveSheet.Cells(row, 1).Value
Address = ActiveSheet.Cells(row + 1, 1).Value
CSZ = ActiveSheet.Cells(row + 2, 1)
Loop
End Sub

"Northwoods" wrote:

I have 37, 800 rows and 1 column. Column A is as follows:
A1 George Brown
A2 1234 Street
A3 Browntown, USA 00234
A4 blank
A5 blank
A6 blank
A7 Betty Smith
A8 2345 Avenue
A9 Thistown, USA 00987
And the pattern repeats for the next 37,800 rows. I need to get 3 columns
setup.
A1: Name
B1: Address
C1: City, State Zip
I have a little knowledge of VBA but on this one I don't know where to start.
Any suggestions would be very appreciated.

Thank you everyone,
I am going to try these options and let you know which or if all worked.
Much appreciated

"Jeff" wrote:

Try this and then you can just delete column A it has finished and you should
have name in column A, address in column B, and city, state, zip in column C.

Sub Jeff()
Dim Name As String
Dim Address As String
Dim CSZ As String
Dim row As Long
Dim row1 As Long

row = 1
row1 = 1

Name = ActiveSheet.Cells(row, 1).Value
Address = ActiveSheet.Cells(row + 1, 1).Value
CSZ = ActiveSheet.Cells(row + 2, 1)

Do While Name < ""
ActiveSheet.Cells(row1, 2).Value = Name
ActiveSheet.Cells(row1, 3).Value = Address
ActiveSheet.Cells(row1, 4).Value = CSZ

row = row + 6
row1 = row1 + 1

Name = ActiveSheet.Cells(row, 1).Value
Address = ActiveSheet.Cells(row + 1, 1).Value
CSZ = ActiveSheet.Cells(row + 2, 1)
Loop
End Sub

"Northwoods" wrote:

I have 37, 800 rows and 1 column. Column A is as follows:
A1 George Brown
A2 1234 Street
A3 Browntown, USA 00234
A4 blank
A5 blank
A6 blank
A7 Betty Smith
A8 2345 Avenue
A9 Thistown, USA 00987
And the pattern repeats for the next 37,800 rows. I need to get 3 columns
setup.
A1: Name
B1: Address
C1: City, State Zip
I have a little knowledge of VBA but on this one I don't know where to start.
Any suggestions would be very appreciated.

Good Afternoon Jeff,
It took me a while to go through all the post options. Your solution was the
only one I could get to work. Thank you very much for replying. I would
really like to know what all this means. Can you point me in the right
direction?
Thanks again
Northwoods

"Jeff" wrote:

Try this and then you can just delete column A it has finished and you should
have name in column A, address in column B, and city, state, zip in column C.

Sub Jeff()
Dim Name As String
Dim Address As String
Dim CSZ As String
Dim row As Long
Dim row1 As Long

row = 1
row1 = 1

Name = ActiveSheet.Cells(row, 1).Value
Address = ActiveSheet.Cells(row + 1, 1).Value
CSZ = ActiveSheet.Cells(row + 2, 1)

Do While Name < ""
ActiveSheet.Cells(row1, 2).Value = Name
ActiveSheet.Cells(row1, 3).Value = Address
ActiveSheet.Cells(row1, 4).Value = CSZ

row = row + 6
row1 = row1 + 1

Name = ActiveSheet.Cells(row, 1).Value
Address = ActiveSheet.Cells(row + 1, 1).Value
CSZ = ActiveSheet.Cells(row + 2, 1)
Loop
End Sub

"Northwoods" wrote:

I have 37, 800 rows and 1 column. Column A is as follows:
A1 George Brown
A2 1234 Street
A3 Browntown, USA 00234
A4 blank
A5 blank
A6 blank
A7 Betty Smith
A8 2345 Avenue
A9 Thistown, USA 00987
And the pattern repeats for the next 37,800 rows. I need to get 3 columns
setup.
A1: Name
B1: Address
C1: City, State Zip
I have a little knowledge of VBA but on this one I don't know where to start.
Any suggestions would be very appreciated.

Thank you for your response but I couldn't get the formula to work. When
inputting the formula in b2, c2 or d2 I received a row reference error
message which pointed back to (Row(R[-1]C[-1])) with Row[reference] error. I
literally used copy/paste so that I wouldn't goof.
I am not familiar with this function, so I am lost.
Thank you
Northwoods
"Gazeta" wrote:


U¿ytkownik "Northwoods" napisa³ w
wiadomo¶ci ...
I have 37, 800 rows and 1 column. Column A is as follows:
A1 George Brown
A2 1234 Street
A3 Browntown, USA 00234
A4 blank
A5 blank
A6 blank
A7 Betty Smith
A8 2345 Avenue
A9 Thistown, USA 00987
And the pattern repeats for the next 37,800 rows. I need to get 3 columns
setup.
A1: Name
B1: Address
C1: City, State Zip
I have a little knowledge of VBA but on this one I don't know where to
start.
Any suggestions would be very appreciated.

in b2 insert formula
=INDIRECT(ADDRESS(ROW(R[-1]C[-1])*6+1,1))
c2
=INDIRECT(ADDRESS(ROW(R[-1]C[-1])*6+2,1))
d2
=INDIRECT(ADDRESS(ROW(R[-1]C[-1])*6+3,1))
and copy them down as u get all addresses
mcg
mcg

Thank you for your response but I couldn't get the 2nd sort to work. I had
#REF! errors that prevented a proper sort. Where did I go wrong?
Northwoods

"JVL_DarkHorse" wrote:

Oops. The instructions I gave you will give you blanks in the first record's
address and city, state, zip fields. Just copy and paste them in prior to
deleting columns A:C at the end.

"JVL_DarkHorse" wrote:

If you are only a little familar with VBA, I recommend not using it for this
example since you cannot undo it (although you can save multiple versions).

In B1:B6 place a 1. In cells C1:C6 place a 1,2,3...6.
In B7 place the following formula "=B1+1".
In C7 place the following formula "=C1".
Drag both of these cells down to the end of your document.
Sort A1:C37800 by column C, then by column B.
Delete the last half (rows 18901:37800- this deletes anything with a 4,5,6
in column C- since this is all blank).

Now copy cells A1:A18900 (this should now be the last cell with data in it).
Paste it in cells D1, E2, and F3 (it will look like a staircase).
Delete rows 18901,18902.
Sort A1:F18900 by column C, then B.
Delete the last two thirds (rows 6301:18900).
What is left should be exactly what you are looking for. You can then
delete columns A,B,C to move D,E,F into the position that you want.


"Northwoods" wrote:

I have 37, 800 rows and 1 column. Column A is as follows:
A1 George Brown
A2 1234 Street
A3 Browntown, USA 00234
A4 blank
A5 blank
A6 blank
A7 Betty Smith
A8 2345 Avenue
A9 Thistown, USA 00987
And the pattern repeats for the next 37,800 rows. I need to get 3 columns
setup.
A1: Name
B1: Address
C1: City, State Zip
I have a little knowledge of VBA but on this one I don't know where to start.
Any suggestions would be very appreciated.