Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Compiling and evaluating data from numerous excel files and worksheets

Posted on 2012-03-13
4
Medium Priority
?
285 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 750 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 750 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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 …

670 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