Moving Cells via code? 3

[MHadden]

Hello All:
I have an excel spreadsheet project that requires me to change the information from this format:

Magnolia House
100 4th Ave W
Oneonta
AL
35121

St Martin's In The Pines
10 Woodside Dr
Birmingham
AL
35210

Shelby Ridge Nursing Home Rehab
1000 1st St N
Alabaster
AL
35007

To this format:

Magnolia House 100 4th Ave W Oneonta AL 35121
St Martin's In The Pines 10 Woodside Dr Birmingham AL 35210
Shelby Ridge Nursing Home 100 1st St N Alabaster AL 35007

I have been doing it manually, but it takes forever! Any ideas that would make it quicker / easier?

Thanks in advance for any help!
- Michael



MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[VulcanJedi]

copy.
paste special
TRANSPOSE

Tranpkp

 

[TonyJollans]

If all the addresses are five lines then ..

(assuming your addresses are in column A)
In row 1 of an empty column enter:
[blue][tt] =IF(A6="",A1&" "&A2&" "&A3&" "&A4&" "&A5,"")[/tt][/blue]
Copy the formula down as far as the addresses go

You will then have the addresses as you want them in the new column - where you can filter on something like <> "" if you want.
If they are not quite that regularly organised it may be a tad more awkward but it can be done without code.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[MHadden]

Both methods work great!
Thank you both for the help.

This is going to save me hours of work, I can't thank you enough!

- Michael


MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[macropod]

Hi Michael,

Here's an even easier way. Copy the following formula to A1 on a new worksheet. Change 'Sheet1' to your data worksheet's name, then copy across to E1 and down as far as needed (1/6th the number of rows on your existing sheet):

=IF(OFFSET(Sheet1!$A$1,INT(ROW()-1)*6+COLUMN()-1,)="","",OFFSET(Sheet1!$A$1,INT(ROW()-1)*6+COLUMN()-1,))

Cheers

[MS MVP - Word]

 

[MHadden]

macropod,
Thanks, you're right - this is much easier! Thanks for this method.
I really do appreciate all of you that have helped!

Thanks, Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!