Solved

Compiling and evaluating data from numerous excel files and worksheets

Posted on 2012-03-13
4
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

623 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