Post Project
  • Search
    • Buyers can
    • Search offers to buy now
    • Search freelancers to request a proposal
    • Freelancers can
    • Search projects to quote on
  • How it works
  • Log in
  • Sign up
  • Freelancer?
Browse by Category
    Technology & ProgrammingWriting & TranslationDesignDigital MarketingVideo, Photo & ImageBusinessMusic & AudioMarketing, Branding & SalesSocial Media

    Excel VBA Macro writing

    - or -

    Post a project like this
    1812
    $$
    • Posted: 5 years ago
    • Proposals: 6
    • Remote
    • #1560019
    • Awarded
    Valeriy S.
    Valeriy S.
    Qlikview, Xcelsius, Excel and SAP BPC developer
    Switzerland Sementina
    Jon G.
    Jon G.
    IT Manager/Developer
    United Kingdom Louth
    Ian C.
    Ian C.
    Database Developer
    United Kingdom Leicester
    Les K.
    Les K.
    IT Support, ACCESS, EXCEL & VBA Developer
    Top Endorsed
    United Kingdom City of London
    Dave G.
    Dave G.
    Microsoft Office and Web Developer
    Top Endorsed
    United Kingdom Leighton Buzzard
    Baran B.
    Baran B.
    Excel VBA, MS PRoject Expert, Web Scraper, Arena, Simulation, Spreadsheet, Wordpress customisation
    Top Endorsed
    Turkey Istanbul
    35147024658227619335495506661300449
    Valeriy S.
    Jon G.Ian C.Les K.Dave G. + 1 other have already sent a proposal.
    • 1
    • 1

    Description

    Experience Level: Intermediate
    In brief, write VBA to: copy 1 column (J) from each of ~40 files in single directory, paste into new spreadsheet ("Masterfile"), apply user-editable scoring definitions at "category" and "class" levels, consolidate into final spreadsheet

    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.
    Colin B.
    96% (5)
    Projects Completed
    4
    Freelancers worked with
    4
    Projects awarded
    75%
    Last project
    15 Feb 2018
    United Kingdom

    New Proposal

    Login to your account and send a proposal now to get this project.

    Log in

    Clarification Board Ask a Question

    • 05 May 2017Vishal K.

      Hi Colin

      The proposal stands for the updated Job Description.

      Thanks
      Vishal K.

    • 05 May 2017Simon C.

      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 2017

      Hi 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.

    • 05 May 2017Mark R.

      I have a few questions...Can we discuss this further,

      thanks

    409583409485409468
    1812
    $$

    - or -

    Post a project like this
    Colin B.
    Colin B.
    96% (5)
    Projects Completed
    4
    Freelancers worked with
    4
    Projects awarded
    75%
    Last project
    15 Feb 2018
    United Kingdom

    Related project Searches


    data structures spreadsheets VBA programming language

    Product

    • About
    • Team
    • Careers

    Support

    • How it works
    • Trust & Safety
    • Help Centre

    Discover

    • GuidesStoriesNews

    Resources

    • Customer Stories
    • Business Cost Calculator
    • Startup Cities

    Browse

    • Freelance Services
    • Freelance Services By Country
    • Freelance Skills
    • Terms
    • Privacy
    • Sitemap
    • © 2022 People Per Hour Ltd
    We use cookies to improve your experience and our services. By using PeoplePerHour, you agree to ourCookie Policy