address list

S

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.
 
D

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
 
R

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)
 
S

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
 
R

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top