Hi, good day ! Thanks for viewing my project.
This VBA macro is work on Excel2010 and later. Details is as follows.
[VBA Macro Overview]
1. Input source data is [login to view URL](this is sample input file) which includes user’s results. From A1 to D2 is question ID, and the following rows is user’s answer.
2. Final output data is [login to view URL](this is sample output file).
3. This purpose of macro is for automating routine work as below procedure.
[VBA Macro Procedure]
1. User can select any file which includes user’s results with dialog, and used as input source data.
2. Macro procedure is executed as following STEP 1 to 8.
3. After completion of STEP 1 to 8, Input source data is save as new output file.
At the time, user can select output file path with dialog.
-----------------------------------------------------------------------------------
STEP 1 BUILD TABLE FOR VLOOKUP
Sheet “vlookup_data” is generated from [login to view URL] which is already inputted prepared.(from cell B24 is name, from cell D24 is number) This sheet is used at STEP 3.
STEP 2 BUILD TABLE FOR VALUE CONVERSION
Sheet “score” is generated from VBA macro, and all value is fixed in VBA code. This purpose of this table is, several question ID is needed value conversion at STEP 5.
STEP 3 INSERT NEW ROW FOR NAME
Insert new row at column ”A”, and input “name” using vlookup with key "user id"(Column B).
STEP 4 COPY ORIGINAL DATA
Copy original data (from Column D to Column BH) to BJ.
STEP 5 VALUE CONVERSION
Execute value conversion at
Columns BJ to BP (Question A1-A7)
Columns BT to BV (Question A11-A13)
Columns BX (Question A15)
Columns CA to CC (Question B1-B3)
with using vlookup from sheet “score”.
Example)
1 -> 4
2 -> 3
3 -> 2
4 -> 1
STEP 6 SCORE CALUCULATION
Column DO(SCORE 1) is sum value from Column CA to Column DC(Question B1-B29).
If score is 77 point or over, cell background and font color is RED using conditional formatting.
Column DP(SCORE 2) is sum value from Column BJ to Column BZ(Question A1-A17), and Column DD to Column DL(Question C1-C9).
If score is 76 point or over, cell background and font color is GREEN using conditional formatting.
STEP 7 MARKING
CONDITION 1) If column DO cell is RED, input “TRUE” in column DQ cell.
CONDITION 2) If column DP cell is GREEN and value of DO cell is 63 and over, input “TRUE” in column DQ cell.
CONDITION 3) If except value (from 1 to 4) is inputted from column BJ to column DL(Question A1-C9), input “ERROR” in column DQ cell.
Example)
1|3 -> ERROR
Blank -> ERROR
STEP 8 CATEGORIZE CLASS
CONDITION 1) Column DO cell color is RED, and DP cell color is GREEN, input “4” in column DR cell.
CONDITION 2) Column DO cell color is RED, and DP cell color is not GREEN, input “2” in column DR cell.
CONDITION 3) Column DO cell color is not RED, and DP cell color is GREEN, and DQ cell value is “TRUE”, input “3” in column DR cell.
CONDITION 4) DQ cell value is “ERROR”, input “5” in column DR cell.
CONDITION 5) Except of CONDITION 1 to 4, input “1” in column DR cell.
Automating Questionnaire Collection as described.
Basically creating a macro (Excel 2010 or later - it will work even in lower versions), that will import 2 files (input and lookup file) and generate an output file as described in attached "project_details" word document.
I m serious freelancer, and i've created / automated, hundreds of Excel VBA projects and i've never had a single problem, and i believe i can automate this one also.
Thanks, Milan
$88 USD in 0 day
5.0 (7 reviews)
4.1
4.1
9 freelancers are bidding on average $166 USD for this job
Greetings,
I can help you in EXCEL VBA PROJECT. Please ping me for more discussion. Please see my profile i have deliver almost 280 projects with 100% client satisfaction.
I have more than 5 years of experience in Excel Macros, Excel VBA and can deliver your with 100% quality.
I am not random bidder.. I hope I can do this one and I do not outsource the work.. Please ping me if you are interested with my proposal and thank you..
Immediate availability.
Responsible, fast and accurate.
Full-time freelancer.
Excel expert. 100% satisfaction guaranteed. Available to any help after completion.
Experience of 6 years using excel for organizing and analyzing any company variables.
Experience on programming with VB in excel, creating any report using pivot tables and pivot charts, dashboards.
Hi, there
I hope all's well with you.
I see you'r looking for a developer proficient in Ms Excel/VB.
Really all my projects here I've developed for Ms Access/Excel/VB
projects.
I viewed your requirement and Excel files. With your guide/explanation of tasks I'll do your current Ms Excel project very professionally and on time.
Here is my introduction:
I’m a ms Access/Excel/VB programmer, a mathematician and a full time
developer with a strong background developing a wide range of accounting,
economical, statistical and mixed type projects using Ms Access, Ms Excel,
VBA, ADO, SQL , including projects for start-up companies and small businesses.
For over the last 20 years I’ve worked in a variety of industries.
I have a deep passion for coding and guarantee that all of developing is 100% original.
I highly value professionalism and hold myself strictly accountable to represent my client’s
brand. I aim to form a long-term working relationship.
Please, let me know what is needed to secure this bid!
Thank you for your consideration.
Zura
Hello,
I have been working as freelancer since last ~ 5 years,prior to this was working with a MNC as an Sr. Software Professional.
Below are some key areas of my skills:
-- VBA and Excel,Word,Office Applications
-- Expert on C# using .NET framework using Visual Studio 2012 and 2013
-- Expert in VB.NET using VS2012/VS2013
-- WPF and Windows Forms Application
-- Expert in ASP.NET,MVC
-- JS,Google extensions and google scripts
I have experience in all the above areas and during my project work I have completed all of the above tasks.
As a Freelancer also I have worked in few of the above areas where I got a chance and delivered the best.
Delivering best & Customer satisfaction is the key for any individual and I always worked keeping these things during project execution.
Would be looking forward to work on this opportunity and will ensure to deliver best.
Regards,
Abhinov