Change address from vertical to horizontal

[bigburgo]

Hi,

I have a whole heap of address listed vertically in an excel document that I need running horizontally.

For example:

School
1st Address Line
2nd Address Line
555-5555
555-5555
Name
Name
School Type
Website address
School
1st Address Line
2nd Address Line
555-5555
555-5555
Name
Name
School Type
Website address

(I have listed two addresses as you can see their is no gap between the address's)

to:

School / 1st Address Line / 2nd Address Line / 555-5555 / 555-5555 / Name /Name / School Type / Website address
School / 1st Address Line / 2nd Address Line / 555-5555 / 555-5555 / Name /Name / School Type / Website address


Anyone know a marco I can use etc?
Or another other way to do this quickly instead of cutting and pasting a couple of thousand times?

Cheers

Rob.




Changed to

 

[MikeWx]

I named the vertical list range SCHOOLDATA. Type the following formula where you want the horizontal version to start, then copy it across to the next eight columns. It will start displaying empty lines when you reach the end of SCHOOLDATA. Since it uses the IFERROR function, you need at least Excel 2007.

=IFERROR(INDEX(SCHOOLDATA,COLUMN(A1)+(9*ROW(A1)-9),1),"")

Mike

 

[bigburgo]

Thanks Mike,

I am using Excel for Mac 2011.

I however have no idea how to work this

=IFERROR(INDEX(SCHOOLDATA,COLUMN(A1)+(9*ROW(A1)-9),1),"")

I pasted it into where I wanted it to start and to next 8 colums but nothing happened.

NOt sure how to make it work.

Regards,

Rob.