Solved

Compiling and evaluating data from numerous excel files and worksheets

Posted on 2012-03-13
4
280 Views
Last Modified: 2012-03-23
Hi

I expect to receive multiple tenders (over 100) in which price rates are submitted electronically in forms on multiple worksheets in an excel file I issued.
Need to evaluate the rates for various items in each form, in which a score will be given based a formula such as: score (for tenderer for an item) =[lowest price (of all tenderers) (for item) / [price by tenderer (for item)].  eg If the lowest price for an item is $100, and a tenderer submits a price of $200 for that item, he/she would get a score of 0.5.
Whats the best way, and can you explain briefly how to do, to:
1/ compile the price data from each excel file submitted
2/ evaluate the data so scores can be assigned.

Thanks
0
Comment
Question by:gh_user
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Rartemass earned 250 total points
ID: 37718512
I think this would be best in a macro that you save to the book.xlt file so all spreadsheets have access to the macro.
Then it would be a simple matter of running the macro to show the output based on the form. You can have the macro display the results on screen, or append another file so each row captures the name of the tenderer (from the input file) and enters their score.

Problems will arise if the tenderer doesn't fill it out correctly (eg inputs $2K instead of $2000) but if you trust them to follow your form, and you have validation on the form, then this shouldn't be an issue.
0
 
LVL 6

Assisted Solution

by:reitzen
reitzen earned 250 total points
ID: 37718537
I had a similar project several years ago where each property submitted their budget and I scraped the G/L account data from the individual workbooks into my consolidated workbook.

Since I created each of the property workbooks, I had and advantage of knowing where each data row would reside.  So, I could write my "import" once and use in on each file.

I saved each workbook in a single directory, looped over each file in the directory, appended all of the G/L account data into a table in my consolidated workbook, closed the file, and moved to the next file.

I was able to pull in the data from more than 150 6MB files in about 20 minutes on a 486 system with 512kb RAM.  It may take a little less time on one of today's systems.  :-P
0
 

Author Comment

by:gh_user
ID: 37759951
Thanks for the two responses.
Not sure whether they work or not, as the task is now being undertaken by someone else.
So will split the points between you.
Thanks again
0
 

Author Closing Comment

by:gh_user
ID: 37759953
Didn't get to test out solutions.  So have awarded to both, with best going to first entry.
Thanks
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question