We help IT Professionals succeed at work.

Compiling and evaluating data from numerous excel files and worksheets

gh_user
gh_user asked
on
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
Comment
Watch Question

Life Coach
CERTIFIED EXPERT
Commented:
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.
Commented:
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

Author

Commented:
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

Author

Commented:
Didn't get to test out solutions.  So have awarded to both, with best going to first entry.
Thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.