Excel rows into columns

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Gazeta]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

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

 

[Guest]

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

 

[Guest]

I think there is a simple way to get the results without programming in VBA.
Assuming that there is a pattern that a record will start after every 6
lines, you can do the following:
B1: =A1
C1: =A2
D1: =A3
E1:=A4 (if necessary)
F1: =A5 (if necessary)
G1: = A6 (if necessary)

Select the region starting B1 till G6. Copy (Edit -> Copy) the selection.
Highlight cells B7 till G37800 (the last row must be a multiplier of 6 in
this situation) and paste the formula. Now copy (Edit -> Copy) columns B till
G and paste it as value (Edit -> Paste Special -> Value -> Ok). All formulas
are now value.
If the selection is still highlight, sort this selection on column B (Data
-> Sort -> Column B (so, without header) -> Ok). Now all data is sorted and
grouped. Only delete column A (the original data), and you end up with 6,300
records.

Hope this will be helpfull for next time.

Regards,
Henry