Trouble Combining Differing Data from Different Worksheets

[CBSVOL]

I am having some trouble in combining various data into a single worksheet, and thought someone may be willing to offer some ideas as to how best to proceed.

The ultimate goal is to find a means of placing all of this data into one linear format, so that all pertinent data from each company is displayed in one row, so that all aspects of evaluating the company can be taken into account. While I have made several attempts at combining this data into one sheet, it leaves me with a host of redundancies that will not combine the essential data I need onto one row of information. Making matters more difficult, while some data is in numeric form, other information is text, making a simple combining function impossible. I have attempted a Pivot Table, but there are too many columns to do so (it gives me an error to remove items).

Here's how I started:
I ran several reports based on one aspect of each company, and placed the results into their own respective worksheet. This left me with app. 25 worksheets. On most reports, I was able to obtain both the "Company ID" (a unique series of numbers and characters assigned to each compny), as well as the company name, however, on app. 5 of the reports, I was only able to obtain the company name, but not the associated company ID. So, if there was one unique and universal field amongst all reports, it would have to be based on the company names, and not the company ID's, which I would much rather use in an instance such as this.

I then placed these separate reports into one worksheet by matching up the company name and ID (where it was available), and made a header row that listed each aspect of the individual reports, making my spreadhsheet looks (very basically) like this:

Column A Column B Column C Column D Column E Column F
Co. ID Co. Name Co. Zip Co. Tests Co. Profit Co. Rep
XYZ XYZ, Inc. 25592 21 $55.00 (blank)
ABC ABC, Inc. (blank) 19 $12.00 Larry Sue
(blank) XYZ, Inc. 25592 5 $11.00 (blank)
XYZ XYZ, Inc. 25592 7 $11.00 (blank)
XYZ XYZ, Inc. 25592 42 $11.00 (blank)
(blank) OK, Inc. 37421 (blank) $10.00 Ralph
OKI OK, Inc. 37421 25 (blank) Ralph
(blank) ABC, Inc. 39764 26 $25.00 (blank)
(blank) XYZ, Inc. (blank) (blank) (blank) Jim Bo

Here is what I would like to appear, with all data being combined on one row:
XYZ XYZ, Inc. 25592 75(sum) $22.00(avg.)* Jim Bo
ABC ABC, Inc. 39764 45(sum) $18.50(avg)* Larry Sue
OKI OKI, Inc. 37421 25(sum) $25.00(avg) Ralph

*When performing a calculation of a numeric value, it does not count those blank cells into that average.

What I am now looking at is one spreadsheet that is app. 6,800 columns long, and 75 rows wide.

Any help, assistance, or otherwise would be most appreciated.

 

[Fazza]

Hi,

A query table does this readily. No formulae or VBA required!!

By way of example - save the file, give all the data range a defined name (though not dynamic). Via menu path data, import external data, new database query, Excel files, then find the file name, then the defined name, select the fields you want, proceed to the "finish" step and select the second option "View data or edit query in Microsoft Query", when you see the results set, successively select one field at a time and for each field (other than co. name) repeatedly click the upper case sigma icon to cycle through the available options so that for the co. ID field you end up with MAX, also MAX for zip, SUM for tests, AVG for profit & MAX for Rep. Hit the 'open door' icon to return the data to excel - suggest to a new worksheet.

By necessity I have only outlined an approach. Suggest you read up on query tables as they are simple & powerful.

regards, Fazza