*Deeep breath*

Hello, :)

This is the writings of a person that is just about to go nuts. If
you are reading this than I thank you for being with me right before I
go crazy. I have run in what seems like a simple problem, but in fact
I think is probably just about impossible to solve.

Just for the Record: I have been trying to solve this problem for what
I think now is 3 weeks. I have done (and read) everything I could
fathom but nothing.

On to the problem.

I am a marketing manager and I just started my job. The company I am
working for is extremely disorganized. Hopefully I can fix that (at
least the marketing part). They have handed me a list in a text file
of customers.

The text format is like this.

Company Name
123 Street Name
City, ST 98765 (123) 456-7890

So they asked me to organize it. The first think I thought of was
Excel. The next thing I thought of is how to make this information
useful. Having done mail-merges at my old job I knew that I would
need to break up the information in several intelligent columns.
Something like

Name | Address | City | ST | ZIP | Phone | Extra Stuff | More Stuff

Ahhh so far so good. I'm looking at the clock and its 8:30am. I
thinking I could have this done by 9am and get a second cup of coffee.
Mind you I'm no excel expert, but it seems pretty straight forward.
That was 3 weeks ago.

So whats the problem?

The problem is as such. If the information given to me was in a
constant 3 rows for each address. Well I would have had just a second
cup of coffee, not a third, forth and n'th. The data, some 25,000
entries is not consistent. So what do I find in the text file.

ACME CORP. (always cap.)
123 Acme Road
Johnsonville, WI 12345 (123) 456-7890

FUNNYBUNNY INC
Merryville, WI 12345 (098) 765-4321


Ohhh the pain....

So I didnt give up and have made progress.

FIRST: I took all the entries and imported them into excel like this.

ColA | ColB | ColC

ACME CORP.
123 Acme Road
Johnsonville | WI 12345 | 123) 456-7890

SECOND: I inserted a column on the left and numbered all the lines
sequentially. Sort of like this:

1 | ACME CORP.
2 | 123 Acme Road
3 | Johnsonville | WI 12345 | 123) 456-7890
4 | ACME CORP.
5 | 123 Acme Road
6 | Johnsonville | WI 12345 | 123) 456-7890

Third: I sorted by Phone Number. This grouped all the City, State ZIP
and number rows together at the top and the Company Name and Address
well below it. The first column numbers are of course now out of
order. I kept them there as reference. Thought I might need them.
Looked like this

10 | Johnsonville | WI 12345 | 123) 456-7890
97 | Johnsonville | WI 12345 | 123) 456-7890
61 | Johnsonville | WI 12345 | 123) 456-7890
08 | ACME CORP.
09 | 123 Acme Road
95 | ACME CORP.
96 | 123 Acme Road
59 | ACME CORP.
58 | 123 Acme Road

FORTH: I copied the NAME and Address part to another Sheet. I then
sorted the information. At the upper part were the addresses the
bottom the names. Since the sort puts numbers above letters. Looks
like this: (Second Sheet, not the first)

09 | 123 Acme Road
96 | 123 Acme Road
58 | 123 Acme Road
08 | ACME CORP.
95 | ACME CORP.
59 | ACME CORP.

So far so good =)

FIFTH: I copied the names from the Second Sheet to a column NEXT TO
the City, St Zip Phone number. They lined up perfectly. So it
looked like this.

10 | ACME CORP. | Johnsonville | WI 12345 | 123)
456-7890
97 | ACME CORP. | Johnsonville | WI 12345 | 123)
456-7890
61 | ACME CORP. | Johnsonville | WI 12345 | 123)
456-7890

*breath* Ahhhhh... so far so good. I have 25,000 NAMES and 25,000
City, ST Zip Phone entries.

All I have to do now is just bring over the addresses.
Oh...Ohh....Ughh!!! I only have 24,723. WHAT THE HECK?!?!?
Welll thats where my problem is. A lot of these entries were just
created but no actual address was ever put in.

Why you ask?? Get in line, I've been asking it for about...well the
last 3 weeks now.

__________________________________________________ ___________

By the way, if you are still reading this far, you are a good
person...and you may not believe me....but Thank You. :)

__________________________________________________ ___________

But maybe something can save me. The numbers i put in at the
beginning, they still line up....Hmmm. So I copy/paste in JUST the
Address under the NAME, City, Zip Phone. Looked like this.

10 | ACME CORP. | Johnsonville | WI 12345 | 123)
456-7890
97 | ACME CORP. | Johnsonville | WI 12345 | 123)
456-7890
61 | ACME CORP. | Johnsonville | WI 12345 | 123)
456-7890
09 | 123 Acme Road
96 | 123 Acme Road
58 | 123 Acme Road

I then Sort by ColA and get something that looks like this.

09 | 123 Acme Road
10 | ACME CORP. | Johnsonville | WI 12345 | 123)
456-7890
97 | ACME CORP. | Johnsonville | WI 12345 | 123)
456-7890
58 | 123 Acme Road
61 | ACME CORP. | Johnsonville | WI 12345 | 123)
456-7890

Looking at this really close you will see that I omitted line 96. I
did it to illustrate the problem I have. Not every Customer Entry has
an address field. Most do, but the few that dont, keep me from creating
an intelligent layout.

So I've decided to just scrap the lines that do not have a subsequent
address . I'm mean I dont even really need them.

So here is my question. :(

Looking at the sample above is there a way to instruct Excel, or any
other program for that matter to only keep the lines that have a
subsequent address?

NOTE: I know this problem is a bit confusing and I'm not certain my
explanation is the best. The idea is I just want to remove the lines
that dont have an address attached to them. I want my entire file to
look like this.

ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
123 Acme Road
ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
123 Acme Road
ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
123 Acme Road

AND eventually like this

1. ACME CORP. | 123 Acme Road | Johnsonville | WI 12345 |
123) 456-7890

2.ACME CORP. | 123 Acme Road | Johnsonville | WI 12345 |
123) 456-7890

3.ACME CORP. | 123 Acme Road | Johnsonville | WI 12345 |
123) 456-7890


PAAALLEEEEEEEEEEEASE HELP ME!!! Honestly I appreciate it if you've
read this far. :)


--
LastHair
------------------------------------------------------------------------
LastHair's Profile: http://www.excelforum.com/member.php...o&userid=36467
View this thread: http://www.excelforum.com/showthread...hreadid=562319