data blocks rows to columns

[tom]

I'm sorry to bother you. I have put hours into searching this and still can't find the answer.

I want to take this data:


Business: Writers, Producers, Directors, Advertising
Categories: Writing, Producing, Directing, Music
Media Sources: Film, TV, Multimedia, Radio, Computer/New Media, Performing Arts, Video, Cable, Music Company Name: The Producers
Contact: Chuck Johns
Street Address: 4534 N. 36th St., #413
City, State, Zip, Country: Phoenix, AZ 86018-3457
Phone Number: 602.468.0533
Fax Number: 602.468.0433
Email: jkk@treknet.net



Business: Writers, Producers, Directors
Categories: Writing
Media Sources: Film
Company Name: ThinkVision
Contact Person: John Fox
Street Address: 2244 North Desmoines #309
City, State, Country: Chicago, IL 60614 USA
Phone Number: 312-348-0032
Fax Number: 312-348-0432
Email: thinkvi@aol.com
Description: Writer of screenplays for film, television and new media. Production services for entertainment, advertising, corporate communications. Services: Writing, Producing, Directing, Image Consultation.


Business: Writers, Producers
Categories: Producing, Writing
Media Sources: Film
Company Name: Salvesen Productions Inc.
Contact Person: Chris lvesen
Street Address: 1002 St. Jon Place
City, State, Country: Cape May, NJ 08204 USA
Phone Number: (909) 898 - 9031 and (609) 884 - 2888
Fax Number: (609) 884 - 0513
Email: Lar@aol.com
Description: Hollywood spec treatments, screenplays, and scripts. Director services. Creative assistance. Internet assistance. Acting and casting. Music and sound creations. Creative production budgets


Business: Writers, Producers
Categories: Entertainment/Creative Arts
Media Sources: Performing Arts
Company Name: Jowler Productions Inc
Contact: Sylvia M. Qcey
Street Address: P O Box 267
City, State, Zip, Country: New Orleans, LA 70186
Phone Number: 502-271-4405
Fax Number: 443-393-7785
Email: SYL@USDA.GOV
Description of Service/Product: J. D. Fowler productions is a production company specializing in gospel musical stage plays.


And put it into a list with columns:

Business Cat. Media Contact Email etc

data data data data data data data data data data data data data data data
etc. etc.


I want this in a normal excel list format so I can mail merge
This message was edited by tom on 2002-09-21 09:06

 

[royUK]

Hi

Is this information all in one column ? If it is you can copy then go to Edit>Paste Special>Transpose. This will paste your data from columns to rows

 

[tom]

When I try transpose, it includes every heading from each record.

 

[Brian from Maui]

Tom,

Have a look at ASAP utilities at

http://www.asap-utilities

It has a transpose function that might work for you

 

[tom]

Transposing causes them all to end up in one long line across the top. It also includes the headers. I want one set of headers at the top and data in rows beneath. Just like a normal Excel list.

Also, some of the records don't include every header, ie. 'description' or 'contact', etc.

 

[Brian from Maui]

Tom,

There is a Advanced Transposer in ASAP, check it out. If it's not helpful in this case it has other features you might need in the future. I've used the transposer and you can specify a range.

 

[Tom Urtis]

Hi Tom, great first name you have.

One approach may be to divide and conquer. Since you listed 4 blocks, perhaps we can keep it simple, by doing it manually without a macro, with a formula and Text to Columns approach. Consider taking advantage of the standard colon in your data that marks a point at which your headers are to the left, and the list data is to the right.

As an example, if all your data is in column A, then for each of the 4 blocks (this first examples supposes your first block starts in A1), here's a 7 step approach:

Step 1
In B1 enter the formula
=LEFT(A1,FIND(":",A1)-1)
and copy down as needed. This will return the header value.

Step 2
In C1, enter the formula
=RIGHT(A1,LEN(A1)-SEARCH(":",A1))
and copy down as needed. This will return the individual data in cells of that row which you ultimately want listed underneath the headers.

Step 3
Select the B and C column range, and copy > paste special values, to get rid of those formulas.

Step 4
Select the column C range, click on Data > text to Columns, select Delimited & click Next, choose Comma as your delimiter, Text qualifier None, and click Finish.

Step 5
Select B1:where your data ends (depending on the longest length of the list), and copy it.

Step 6
Select whatever cell on your worksheet that you want to be the upper left most cell of the new range, and click Edit > Paste special > Transpose.

Step 7
In your new transposed list, AutoFit the column width and format the data as desired.

Step 8 (optional)
Go grab a beer; it's a football weekend.

 

[tom]

Thanks guys but it still isn't working. I have 600 records(not just the 4 in the example) The ASAP program doesn't seem to do what I need done also. Brian maybe you can advise on that.
Tom thanks for all the time you spent on this.
If anybody knows how to do this, please let me know, Thanks in Advance

 

[Tom Urtis]

Hi tom, may I respectfully make a constructive suggestion.

Those asap utilities are a good resource, and I copied your your data precisely as you posted it, and obtained a solution by performing the 7 steps I posted back to you.

Yet, your problem is still not solved after receiving usefull responses from 3 different people. Sometimes that happens. When it does, take a look at your data and how you posted it, and especially, how you described it. For example:

(1) royUK asked you at the beginning if all this data is in one column, but you did not answer.

(2) You portray your expected reults as:
Business Cat. Media Contact Email etc

data data data data data data data data data data data data data data data
etc. etc.

This could be ambiguous if someone wonders if you want the data listed horizontally or vertically under your headers.

(3) You do not say what result occurs when you try either of the suggestions (mine or asap). You only say they don't work. What exactly are you seeing?

The point is, you can increase the odds in your favor at finding a solution by being more specific with what you are trying to do, what you have done, what result you got, and what result you want. As you said, you spent many hours working on this problem, so it's a good bet no one wants to retrace your steps by offering a result you already got on your own but cannot use.

Hopefully this may help you think of something you are seeing on your computer screen that we are unaware of, that will be an important piece of the puzzle to solve.

Good luck.

 

[Brian from Maui]

Tom,

If I read this correctly, you want all info in column A to be transposed to Row 1, cloumn B to row 2 etc...If this is correct try this...but please BACKUP your original information.

Highlight column A then Go to ASAP/Range/Transpose with formula, then select a cell where you want this info transposed to and do this again for column B etc...