transposing data from a worksheet

[mikemoses]

Hi folks
I have a worksheet with names, titles, address', email and require these to be in rows for import into a database.
the format is like so:
LASTNAME, FIRST NAME - Title, Company
Street Address, CITY, STATE, ZIP
Phone (000)-000-0000
Fax (000)-000-0000
Email johndoe@acompany.com
LASTNAME, FIRST NAME - Title, Company
Street Address, CITY, STATE, ZIP
Phone (000)-000-0000
Fax (000)-000-0000
Email johndoe@acompany2.com
There is no spacing and I did try the macro from an earlier thread, but alas no spaces.
Any help would be apprecitated.
THanks

Mike

[stnkynts]

It is impossible to answer your question since you did not supply an example of how your data is initially, only your desired output. Submit an example workbook which clearly demonstrates before and after examples.

[Ed_Collins]

I gather the format you provided (the five rows) is the desired solution.

If so, it would help if you added a small sample of what your worksheet looks like now, that you'd like to see formatted as above.

Another question I have on the above format is do you want the second line, (Street Address, City, State, and ZIP) all in a single cell on that second row, or do you want Street Address in one column, City in the next column, State in the next column, etc.

Ideally, attaching a small workbook showing the first sheet with the data as you have it now, and a second sheet showing your intended solution, would be ideal.

[mikemoses]

Thanks here is the spreadsheet example

[Ed_Collins]

Okay, so it appears my guess was wrong. Sheet1, and the format you provided in your original post, must be the original data, since it's not consistent. Sheet2, the data broken up into specific columns, is the intended solution.

Sheet1 isn't consistent because I see that sometimes there are seven rows to a record (Zelalem Abebe) and sometimes there's just five rows (Elaine Abode, Teri Abou) and sometimes there's six rows (Jason Accardi).

The solution is certainly do-able... but it will take someone some time. The program (macro) has to be smart enough to parse all of this inconsistent data.

If the phone number will always follow a (P): and the fax number will always follow a (F): and the e-mail address will always follow a (E): then that will help.
The states all seem to be the two-letter state abbreviation, followed by a numeric five digit and sometimes nine digit zip.
It appears the first name always follows the first comma of a new record, on row 1, and everything before that is the last name.

All of this will help someone to write the macro, of course. Hopefully all of the other data is consistent.