Excel VBA Macro writing
- or -
Post a project like this- Posted:
- Proposals: 6
- Remote
- #1560019
- Awarded
Description
Description of excel work: 2 pieces of work:
A) Macro to:
1) Look in resident directory for all Excel files
2) For each file:
- Insert file name to Row A
- copy Column J to masterfile (where macro resides) on Worksheet “Import”
3) Once all data copied, run routine that applies “category scoring rules” generated from Worksheet
See Boolean logic explained in worksheet "Category & Class Scoring"
4) Once category scoring rules applied, run routine that applies “class scoring rules”
5) Fill adjacent columns (i.e. D for first project, with 1 for C, 2 for N, 3 for F, N/A for not applicable, N/K for not known)
6) Generate additional worksheet with Project Name in Row A, and each column corresponding to the C/N/F column originally imported, plus generated category and class values
B) Structuring Boolean logic in Excel such that it is easily (by a non-VBA user) changeable in where the thresholds lie
Possible follow-on work r.e. pivot tables and charts
Extra notes: See attached Excel file for input data structure, and boolean logic of category/class scoring
Colin B.
96% (5)New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hi Colin
The proposal stands for the updated Job Description.
Thanks
Vishal K. -
Hi Colin,
I have read your project specification but I have a few questions to ask before submitting a proposal. They all apply to the macro requirement in part A.
2. Is the file name to be inserted into each of the files that are in the resident directory, or is it written to the masterfile?
When you copy the data into the masterfile, is this temporary for later use, or are you copying the file name and column J from each file into one of the sheets of the masterfile?
4. Where are the classes in the import file? I can't see anything in the sample import file that corresponds to the Class Scoring. Are those items to be added?
6. Is the Project Name the file name from 2 above? When you say you want to create a new worksheet, is this a new sheet within the masterfile or a new Excel file?
Kind regards,
Simon C.Colin B.05 May 2017Hi Simon, answers inline below.
2. Is the file name to be inserted into each of the files that are in the resident directory, or is it written to the masterfile?
- inserted into the masterfile
When you copy the data into the masterfile, is this temporary for later use, or are you copying the file name and column J from each file into one of the sheets of the masterfile?
- all column J's into a single sheet in the masterfile
4. Where are the classes in the import file? I can't see anything in the sample import file that corresponds to the Class Scoring. Are those items to be added?
- They're not in there at present. Class I incorporates Category A and B, Class III Categories I, J, and criteria 118-121, Class II Categories in the middle
6. Is the Project Name the file name from 2 above? When you say you want to create a new worksheet, is this a new sheet within the masterfile or a new Excel file?
- Correct, filename = project name. New worksheet within masterfile
Kind regards,
Simon C.
-
I have a few questions...Can we discuss this further,
thanks