address list

[Sarah]

Hi and I really hope someone out there can help. I have an address list in
one column in Excel. It looks like this

Blank Row
Company Name
Address 1
Address 2
Address 3
Sometimes Address 4
County (I live in England) so equivalent to State
Post Code so equivalent to Zip Code
Tel Number
Blank Row
etc


I want to tranpose it so that I have Company Name in Column 1, Address 1 in
Column 2, Address 2 in Column 3 and so on. I have looked in the forums and
there are solutions for this where there is a regular number of lines for the
address.

My problem is that some of the entries have 3 rows for the full address and
some have 4.

There is a blank row between every entry.
Every tel number is preceded by the characters Tel:
The line before every tel number is always the Post Code
The line before the Post Code is always the County

I would obviously like the columns to line up so that I can filter by County
or Post Code.

Is anyone able to help with this please?

All suggestions gratefully received.

 

[Dave]

Sarah,

Select your data and copy.
Select the blank row above your data (This shouldn't have been copied)
Edit|Paste special
Check (or as we say in England tick) transpose
Click OK

Mike

 

[Sarah]

Hi Dave, sorry, I should have said, I have about 1000 entries !!!

 

[Ron Coderre]

With
A1:A1000 containing your posted structure

Blank Row
Company Name
Address 1
Address 2
Address 3
Sometimes Address 4
County (I live in England) so equivalent to State
Post Code so equivalent to Zip Code
Tel Number
Blank Row
etc

Try this:
C1: StartRef
D1: EndRef
E1: CompName
F1: Addr_1
G1: Addr_2
H1: Addr_3
I1: Addr_4
J1: County
K1: PostCode
L1: Phone

C2: =N(D1)+2
D2: =MATCH("Tel *",INDEX(INDEX($A:$A,C2+1):$A$1000,0),0)+C2
E2: =INDEX($A:$A,$C2+COLUMNS($E:E)-1)

COPY E2 across through H2

I2: =IF((D2-C2)=7,INDEX($A:$A,$C2+COLUMNS($E:I)-1),"")
J2: =INDEX($A:$A,$D2-COLUMNS(J:$L)+1)

COPY J2 across through L2

Then....
Copy those formulas (C2:L2) down as far as you need


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Sarah]

Hi Ron, I'm being thick about this. For clarification, do I type in Cell C1
Start Ref, type in Cell D1 EndRef, etc.

Then enter your formulas in C2, D2 and E2.

I tried this and I got a lot of cells with N/A

I am copying over some lines of the real data if that helps

Aberdeen & Northern Eggs Ltd
West Cockmuir
Strichen
Fraserburgh
Aberdeenshire
AB43 4RQ
Tel: 01346-532276

C B Davidson
Little Ardinn
Turriff
Aberdeenshire
AB53 8HN
Tel: 01888-563357

R & E Chapman
North Chapelhouses
Oldmeldrum
Inverurie
Aberdeenshire
AB51 0AW
Tel: 01651872343

 

[Ron Coderre]

Hi, Sarah

I created a demo file using your sample data.
Note: I changed the Col_D formulas to account for phones starting with "Tel:
"

It's at this file sharing location:
http://www.savefile.com/files/1297773

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Sarah]

Fantastic ! This has worked just great and I am very happy now.

Thanks again

 

[Ron Coderre]

I'm glad I could help, Sarah

***********
Regards,
Ron

XL2003, WinXP