Transpose Data Based On Cell Value

[Blessy Clara]

Hi All,

Could somebody please help me with Transposing the data

Data -

21 ACTINOVA, LTD.
Add: 185 Cambridge Science Park Milton
Road Cambridge CBA OGA UK
E-mail: sk@actinova.co.uk
Web site: www.actinova.co.uk
CEO: Dr. Steven Powell
Research director: Dr. Peter Laing
22 ACTIPAC BIOSYSTEMS GMBH
Add: AM Klopferspitz 19 Martinsried/Munchen
D-82152 Germany
E-mail: info@actipac.de
Web site: www.actipac.de
President: Enno Spillner
Research director: Dr. Celal Albayrak
23 A. DAIGGER
Add: 675 Heathrow Dr. Lincolnshire, IL
60069-4200
E-mail: daigger@daigger.com
Web site: www.daigger.com
President: James R. Waldenberg
25 ADAPTIVE BIOSYSTEMS, LTD.
Add: P.O.Box 2203, 15 Ribocon Way
Progress-Park Luton LU4 9FT UK
E-mail: sales@adaptive.co.uk
Web site: www.adaptive.co.uk
CEO: T. J. Bonham Carter
Research director: Dr. C. Taylor
--------
----------
-------
2970 ZYMOGENETICS, INC.
Add: 1201 Eastlake Ave. E., Seattle, WA 98102-3702
President: Susan Specht

Info - There are 2970 Organizations in excel
No blank rows inbetween
Hints - Each Organizaitons Begins with a number (From 20 and goes upto 2970) Highlighted in Red

Requirement
These Data must be transposed.
Is there a possibility to add a blank row before every number - So that I can transpose the data using Blank rows as criteria
Something Like tell excel to recognise if a cell value begins with Number (22, 23, 24-----2970) and when it begins with Number to add a blank row above these Rows containing Numbers)

 

[Blessy Clara]

Or is there a way to transpose the data as per the cell content -
Like
Column A (Org name)
Column B (Add)
Column C (Add 2) For those organizations that have more than Two Add Rows
Column D (Email)
Column E (Website)
Column F (President/CEO or CEO or President) whatever is available for this heading
Column G (Research Director)

Since the Raw Data has these Specific Terms -

If this is possible - then it would be very very helpful.

Thanks

 

[Manohar M G]

Hi,

You want to display address (Org Name, Add, CEO, Email,website etc) in each column right..??

i have some queries:
1. What is Add2 in column C.. What should be displayed.. Can you tell me what is Add2 in this:

"Add: 185 Cambridge Science Park Milton
Road Cambridge CBA OGA UK"

2. Initially in which cell these data will be there..
3. And in which cell u need the output.
4. There is any space or comma or something else will be there between each word (Org Name, Add, CEO, Email,website etc).

 

[moonfish]

There was a topic about this recently:
http://www.mrexcel.com/forum/showthread.php?t=568825

The solutions need to be tailored a bit but the general idea is the same.

 

[Blessy Clara]

Hi Moonfish,

Thank you for the response - I had cross checked the link provided - sseems to be useful.

 

[Blessy Clara]

Hi Manohar

For more clarity

The data to be transposed is in Column A (Sheet1)
Data Pattern -
1) Each Company Name Begins with a number (Starting from 20 to 2970)
2) There are no blanks rows in between
3) The only way to differentiate a new Company is based on this Number

Desirable Transpose pattern
I had checked some online info - I could only get close to general transpose,
But what I am looking for is to transpose based on the cell content

Additional Information
Each data had a hint before

Example

51 AEROGEN

"NUMBER 51 COULD BE TAKEN AS A HINT FOR ORG NAME"

Add: 1310 Orleans Drive Sunnyvale, CA
"Add: IS THE ADDRESS OF THE COMPANY"
THERE ARE FEW COMPANIES THAT HAVE ADDRESS IS TWO COLUMNS " - THIS I HAVE MENTIONED AS ADD 2

94089
Web site: www.aerogen.com
President/CEO: Jane Shaw, Ph.D.
Research director: Robert Fishman, M.D.

THOSE HIGHLIGHTED IN RED ARE FOUND IN COMMON FOR ALL THE 2970 COMPANIES.

REQUIREMENT
IF THIS CAN BE USED FOR TRANSPOSING
Such that

Data is in Column A (sheet1)
So transposed data could be in column B
Such as
Column B (Org name)
Column B(Add)
Column D (Add 2) For those organizations that have more than Two Add Rows
Column E (Email)
Column F (Website)
Column G (President/CEO or CEO or President) whatever is available for this heading
Column H(Research Director)



Example
51 AEROGEN
Add: 1310 Orleans Drive Sunnyvale, CA
94089
Web site: www.aerogen.com
President/CEO: Jane Shaw, Ph.D.
Research director: Robert Fishman, M.D.

52 AERONEX, INC.
Add: 6975 Flanders Dr. Suite ASan Diego, CA
92121
E-mail: gatekeeper@aeronex.com
Web site: www.aeronex.com
President/CEO: Jeff Spiegelman
Research director: Jeff Spiegelman

After Transpose

Org	             Add	        Add2	           Email	Website	President	   Research Director	
51 AEROGEN      Add: *     If any in this col     Email: *       Website:*   (Pr/CEO)*   Research director:*

Based on the Data in Cell - the transposed data to be put under the respective/specific Column
(I am not able to post a image)
I have tried to make it as clear as possible. Please let me know if this is understandable

The normal transpose function just transposes the data and then I have to manually paste email in email column (which sometimes may be transposed in add 2 when add 2 row is not available for few companies)

Thank you