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.