Converting 'vertical' address list to 'horizontal'

kgarry1 · Mar 11th, 2002, 01:55 PM

I have a sheet full of addresses formatted vertically:

A1: Company1 Name
A2: Company1 Address
A3: Company1 City, Zip

A6: Company2 Name
A7: Company2 Address
A8: Company2 City, Zip

I want to show this data horizontally:
A1: Company1 Name
B1: Company1 Address
C1: Company1 City, Zip

A2: Company2 Name
B2: Company2 Address
C2: Company2 City, Zip

I can do this easily for 1 address, but my data source includes over 1000 addresses. I'm unable to get the cell references to offset beyond the 1st record.

Any ideas?
Thanks in advance...

Mar 11th, 2002, 02:20 PM

If your data is uniform (each company consisting of 5 lines) then your can put these formulas in adjacent columns, starting at row 1:

=OFFSET($A$1,5*ROW()-5,0)
=OFFSET($A$1,5*ROW()-4,0)
=OFFSET($A$1,5*ROW()-3,0)

and copy down.

Ian Mac · Mar 11th, 2002, 03:19 PM

A few people help me out with a similar problem, try this link

http://www.mrexcel.com/board/viewtop...ic=369&forum=2

Mar 11th, 2002, 03:19 PM	#3
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	A few people help me out with a similar problem, try this link http://www.mrexcel.com/board/viewtop...ic=369&forum=2 __________________ "Have a good time......all the time" Ian Mac

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 01:55 PM	#1
kgarry1 New Member Join Date: Mar 2002 Posts: 1	I have a sheet full of addresses formatted vertically: A1: Company1 Name A2: Company1 Address A3: Company1 City, Zip A6: Company2 Name A7: Company2 Address A8: Company2 City, Zip I want to show this data horizontally: A1: Company1 Name B1: Company1 Address C1: Company1 City, Zip A2: Company2 Name B2: Company2 Address C2: Company2 City, Zip I can do this easily for 1 address, but my data source includes over 1000 addresses. I'm unable to get the cell references to offset beyond the 1st record. Any ideas? Thanks in advance...

Mar 11th, 2002, 02:20 PM	#2
Guest Posts: n/a	If your data is uniform (each company consisting of 5 lines) then your can put these formulas in adjacent columns, starting at row 1: =OFFSET($A$1,5ROW()-5,0) =OFFSET($A$1,5ROW()-4,0) =OFFSET($A$1,5*ROW()-3,0) and copy down.