data blocks rows to columns

tom

New Member
Joined
Sep 20, 2002
Messages
10
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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

New Member
Joined
Sep 20, 2002
Messages
10
When I try transpose, it includes every heading from each record.
 

tom

New Member
Joined
Sep 20, 2002
Messages
10

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257

ADVERTISEMENT

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

New Member
Joined
Sep 20, 2002
Messages
10
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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...
 

Forum statistics

Threads
1,144,282
Messages
5,723,474
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top