Solved

Compiling and evaluating data from numerous excel files and worksheets

Posted on 2012-03-13
4
276 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Didn't get to test out solutions.  So have awarded to both, with best going to first entry.
Thanks
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now