OK, I'm stuck on another Excel problem.  I got great help last time,
so I thought I would try this forum again.  Here's the question:

I have a csv file that I converted into a regular Excel file.  It is a
list of organizations, with address, phone, and other information
about them.  I would like to make it into a nice Excel spreadsheet,
with a column for every data element.  My problem is that right now
all I have is one long column in Excel, containing all the data.  It
wouldn't be too hard to handle breaking that up into separate columns,
except for one major detail:  the number of data elements varies with
each record!  Each record has a core of 9 data elements (rows) that
are always present.  They are things like name,phone, fax, hours,
city/state/zip, email, and a couple of others.  BUT, the address (not
counting the city/state/zip line) is sometimes one line or sometimes
two, and there are a couple of other lines (corporate ownership and
accreditation) that are sometimes found in a record and sometimes not.
 So, the record size can vary from 9 lines to 12 lines.  And, the
records are separated by blank rows, but it's sometimes 2 rows and
sometimes 3 rows.  So, how can I convert this file into a nice Excel
sheet, with 12 columns, leaving blank a particular column if that info
is not present in the record?

I am looking for a pretty specific answer here, not just general
guidance. If that requires that I supply more details, I'll be happy
to do so.  BTW, I am using Excel 2000 on a PC, in case that's
important.

chanelops-ga,

There may be a more elegant way to solve this problem, but this is how
I go about it.

I've provided an example spreadsheet with a few doctors names that I
downloaded from google (I wanted to find a sample that sounded like it
was similar to your dataset).

http://www2.bc.edu/~lawrenst/Book1.xls

Step 1 is to make sure that your original data is in a single column
as shown in the file.

Step 2 is to locate the end of each record.

To do this, we enter the number 1 into cell B2. This will signify the
start of our first record.

In B3 we enter the following formula :

=IF(ISBLANK(A3),B2,IF(ISBLANK(A2),B2+1,B2))

What this formula does is look at the cell to the left. If it contains
data, it looks at the cell above to see whether it is blank. If it is
blank then this must be the start of a new record, so it updates the
record number by 1.

We then paste this equation to the rest of the column, so that we have
a column of increasing numbers, the increments occuring at the start
of a new record.

Step 3 - Getting the name copied to the right column.
This step is fairly straightforward.
In cell C2 we enter the following formula :

=IF(B2=B1,"",A2)

We copy this all the way down that column. What this equation does is
compare our record numbers in column B. If we are at the start of a
new record (ie. the record number does not match the number above it)
then we copy the value from the cell directly to our left in column A.
This should be the name.

Step 4 - First line of the address.
This step is just as straightforward as the following line should
always be the address. We enter into D2 the following formula :

=IF(B2=B1,"",A3)

We copy this all the way down the column. This checks to see if we are
at the start of a record and then copies the second line of the
record.

Step 5 - Potential second line of the address.
This is where I have to be general and let you fill in the blanks to
this sentence :
"I can tell whether a line is the second line of an address or the
city/state/zip because the address does not contain the character
______________"

In my example, we can assume that if the line contains a comma, then
it is a city/state/zip.

In cell E2 which will be an intermediate column, we use the following
equation :

=IF(B2=B1,"",SEARCH(",",A4))

This returns the location of the comma in cell A4 or #VALUE! if no
comma is found.

Step 6 - Finding the split between City and State otherwise
In cell F2 enter (and copy down the whole column):

=IF(ISERR(E2),SEARCH(",",A5),"")

This cell looks to see whether we found a comma before (by looking at
cell E2). If E2 contains #VALUE! (ie. it has an error in it) then it
does the search on cell A5, which is the next most likely location for
the city.

Step 7 - Second Line of Address if Necessary
In cell G2 enter (and copy) :

=IF(ISERR(E2),A4,"")

This looks at cell E2 to see whether A4 was a city/state/zip field. If
it wasn't then it copies the value here.

Step 8 - City
In cell H2 enter (and copy) :

=IF(ISNUMBER(E2),LEFT(A4,E2-1),IF(ISNUMBER(F2),LEFT(A5,F2-1),""))

This takes everything to the left of the comma as the city from the
relevant row.

Steps 9 and 10 - State and Zip

In I2 :
=IF(ISNUMBER(E2),MID(A4,E2+2,2),IF(ISNUMBER(F2),MID(A5,F2+2,2),""))

In J2 :
=IF(ISNUMBER(E2),MID(A4,E2+5,5),IF(ISNUMBER(F2),MID(A5,F2+5,5),""))

Similar to Step 8 - this returns the 2 digit State and the 5 digit zip

Step 11 - Email
Do this in two parts :
In K2 enter :

=IF(ISERR(SEARCH("@",A2)),IF(B2=B3,K3,""),A2)

In L2 enter :

=IF(B2=B1,"",K2)

This step assumes that only email addresses contain "@". It searches
each row for an @ and passes on the most recent email address.

Step 12 - Phone
In M2 enter :
=IF(ISERR(SEARCH("-",A2)),IF(B2=B3,M3,""),A2)

In N2 enter :
=IF(B2=B1,"",M2)

SO NOW WE HAVE A RECORD FOR EACH DOCTOR ON A ROW. NOW TO TIDY IT UP

Go to Sheet2 to see what I did.
Column A contains increasing numbers
Column B does a search for the row on which a record can be found
Column C returns the name associated with that row
Columns D thru J do the address, etc.


You may need to experiment with this spreadsheet to get it to work for
you. But if my descriptions make sense, then you should be able to
adapt it to your needs. Let me know if anything in my answer is
unclear and I will be happy to clarify it for you.

Regards

CalebU2-ga

---

Clarification of Answer by calebu2-ga on 05 Nov 2002 21:32 PST

chanelops,

Glad you were able to get it to work for you (almost!).

As for the problem with the "non-empty" blank cells, one possibility
is to replace the statement ISBLANK(A2) with LEN(A2)=0.

If A2 is truly blank then it will have a length of 0. If it contains
hidden characters, then its length will be >0.

That should solve your problem - let me know if there are any further
questions.

Good luck

Calebu2-ga

It took me a while to 're-code' this to match my particular data set,
but I did, and it works great.  So thanks very much for a good answer.
 One weird problem that I am having, however, is that in order to have
the blank lines between the records, I had to eliminate some junk that
was on one of those lines.  (The other line or two were blank
originally)  But now, that cell that had data in it fails the ISBLANK
test, even though no data is visible.  (I took out the unwanted info
and wrote "" back into the cell)  I have tried various ways to make it
pass the ISBLANK test, but the only thing that seems to work for sure
is to go back and just delete the cell, manually, which is a pain.  Is
there some other, more automated, way to solve this little wrinkle?? 
If so, that would make this a 5-star answer for me!

That was an awesome solution.

thekirklands-ga :

As I said in the answer, there is probably an easier way of doing it
(especially for name and address data. What makes the answer cool is
that it highlights a set of tools that you can use to reorganize
practically anything in excel. The commands SEARCH, INDEX, MATCH,
OFFSET, LEFT, MID, RIGHT, ISNUMBER, ISBLANK, ISERR and IF are
extremely powerful commands when you have an idea of how they work.
(They are also extremely easy commands to screw up, so I strongly
suggest testing your answer on a few examples first before trusting it
to your whole database :)

Regards

calebu2-ga

Hi chanelops,
      Your problem can be easily solved by writing a vba macro. To do
that I need the full structure or sample data from xls sheet. Can you
please paste the data or give the sample xls for downloading in a
common server.

From the sample data I need to understand the following
1.What are the fields in each row.
2.If data is not there for a particular field(say address line 2), it
it left blank or replaced by next field?
3. If there is no data and the line is left blank, then easy to code.
If not how to identify which field these data belongs to? Is there any
way?

Thanks,
RAC

RAC, thanks, but I'm happy with the answer above.  Although it may not
be the most elegant, it works for me.