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
Source , ID
UK , 1234
USA , 5678
AUS , 9012
USA , 1234
ID , Score , Condition1 , Condition2
1234 , 10 , Y , Y
5678 , 7 , N , N
9012 , 4 , Y , N
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
a. Open 3 workbooks from file
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.