converting from worksheet to rows

[gregh]

Hi,
I have a spreadsheet that contains information on several hundred companies. It is currently arranged so that each company is on its own worksheet. To make life more complicated, while each sheet has the same information, this information is not in the same cells in every sheet. For instance, part of the information is the list of the company's board of directors. Since companies have varying numbers of directors, this pushes the information down further for some companies.

What I would like to do is to have all the information on one worksheet arranged so that each firm is in one row. Since all the data is labeled the same, I am thinking some sort of macro should work, but I am not really sure of how to start.

Any suggestions?

Thanks

 

[Smitty]

Welcome to the Board!

Can you give an example of the data structure? Colo's HTML Maker will let you post a shot of a sheet. There's a link at the bottom of the page.

Smitty

 

[gregh]

Hopefully this will work. This is just part of the sheet, but it will give you an idea of what it looks like. This particular example only has one principal shareholder, but some have multilple, some have none. That is why the data below it is in different cells in different sheets.

Thanks

GOV_PILLDATAJohnspart.xls
	A	B	C	D	E	F	G	H
1	CompanyName			Anoucementdate	ProxyMailingDate	ShareholdersmeetingDate
2	CSResources			07/06/1997	27/03/1997	13/05/1997
3
4	IsPillRenewalorNew:	NOInfo
5
6	NumberofShares	26,662,329
7
8
9
10
11	MultipleClassesofShare	No
12
13	PrincipalShareholders(>10%)			#ofShares	%ofgroup
14	ConnorClark&LunnInvestmentManagementLtd.			2,700,300	10.13%
15
16	StockownershipofUnaffiliatedBlockholders(OtherthanmanagementandDirectors)				10.13%
17
18	BoardData
19	Size(#ofDirectors)	9
20	CEO=Chairman	yes
21	CEOTenure	13
22	StockOwnershipofExecutivesandDirectors	2.90%		772053
23
24
25	#ofOutsideDirectors	6
26	FractionofOutsideDirectors	66.67%
CS resources

 

[gregh]

that didn't seem to work. Any idea why?

 

[Smitty]

Read the HTML Maker FAQ

Essentially, when the new browser window opens you need to select "View Code", copy the html code that appears in the new window and past that into your post. That'll get rid of the "&nbsp"'s.

Smitty

 

[gregh]

Let's give this a shot. Thanks again for your help.

GOV_PILLDATAJohnspart.xls
	A	B	C	D	E	F
1	Company Name			Anoucement date	Proxy Mailing Date	Shareholders meeting Date
2	CS Resources			07/06/1997	27/03/1997	13/05/1997
3
4	Is Pill Renewal or New:	NO Info
5
6	Number of Shares	26,662,329
7
8
9
10
11	Multiple Classes of Share	No
12
13	Principal Shareholders (>10%)			# of Shares	% of group
14	Connor Clark & Lunn Investment Management Ltd.			2,700,300	10.13%
15
16	Stock ownership of Unaffiliated Block holders (Other than management and Directors)				10.13%
17
18	Board Data
19	Size (# of Directors)	9
20	CEO=Chairman	yes
21	CEO Tenure	13
22	Stock Ownership of Executives and Directors	2.90%		772053
23
24
25	# of Outside Directors	6
26	Fraction of Outside Directors	66.67%
CS resources

 

[gregh]

any thoughts?

Thanks