Help With VBS | Multiple File input, Dictionary Entries vs VLOOKUP, INDEX and MATCH

  • Hello, I am after a little help.


    I process an excel file(Workbook.data), add information from separate file(workbook.KB) using VLOOKUPS, and use this combined data to create a result, using values from separate Criteria (workbook.criteria) with an INDEX and MATCH condition. This is very time consuming and reliant on myself to process. I am after a way to automate these manual tasks and quickly. I have tried the separate each task by the vbs script to run a single VLOOKUP against 10,000 lines takes an age. Please find the sample files, data, requirements, and output below.


    1. Source Data


    Workbook.data
    Source , ID
    UK , 1234
    USA , 5678
    AUS , 9012
    USA , 1234


    workbook.KB
    ID , Score , Condition1 , Condition2

    1234 , 10 , Y , Y
    5678 , 7 , N , N
    9012 , 4 , Y , N


    workbook.criteria
    Result , Source , Condition1 , Condition2 , Min Score , Max Score
    C1 , UK , Y , Y , 8.1 , 10
    M2 , USA , N , N , 5.1 , 10
    H1 , USA , Y , Y , 8.1 , 10
    L1 , AUS , Y , N , 1 , 5


    2. Requirements


    a. Open 3 workbooks from file
    data
    KB
    criteria


    b. Evaluate each row in workbook.data till last ID(dynamic number of rows) using the below conditions and output results to new Column (location not important) for each row.


    Result in workbook.data = When Source and Condition1 and Condition2 and the Score is (above and below) value output


    ID is the constant between workbook.data and workbook.KB to retrieve the Score, Condition1, and Condition2
    Then this data with the Source is used to query the workbook.criteria to retrieve the Result


    Final Output in workbook.data
    Source, ID , Score , Condition1 , Condition2 , Result
    UK , 1234 , 10 , Y , Y , C1
    USA , 5678 , 7 , N , N , M2
    AUS , 9012 , 4 , Y , N , L1
    USA , 1234 , 10 , Y , Y , H1



    I would appreciate any help. I can find examples of separate requests, but is bringing it all together. This would really help me not have to work Bank holidays to process each week.


    Best Regards

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!