Hello
this is my first post so apologies in advance if I am breaking any rules or if my post is not 100% informative.
Here is how the process is manually:
There are 3 spreadsheets. Master log, 2013, and 2014. Master logs comes from a finance-owned system, 2013 & 14 come from the employees of the warehouse.
All files contain a list of order numbers, product identifier numbers, and units quantity, amongst other information.
A member of our team has to compare if the information between master log and 2013/14 match.
Here is the process manually:
1) Open all 3 files.
2) Select master log.
3) Copy first order number.
4) Search on 2013 file (entire workbook) - if order number is found, read accross the line to check if product code is the same. If it is, check if case number is the same. If yes, write on master log "logged".
5) If product code is not the same, search next until found on either 2013 or 14.
6) If order and product code combo is found, but cases are different, write on master log "logged with case discrepancy".
7) If order and product code combo is not found, write on master log "not logged".
Then the "not logged" entries are sent on a separate spreadsheets.
I am wondering what is the best way to do it with my amateur vba skills.
First option:
Use vba to put a formula to a new comparisson column on the master log.
I am thinking a combination of index(match) formulas to return the number of cases, then compare the number of cases with what's already on the master log.
The problem I have with this option is that the formula must check a total of 10 tabs (5 on 2013 sheet, 5 on 2014 sheet), so it will be both hard to write and as a gut feeling, it sounds inefficient, as it has to check for two different cells (if cell1 = value1, then if cell3 = value2, then return cell4 etc).
When I did something similar on a different file, I used vba to put an extra column to do a concatenate so the index match would be easier, but I don't think its useful to do on 10 tabs.
Second option:
Use a macro that replicates what "search" does, then do a formula - however I am unsure on how to do this.
Third option?
I would appreciate your suggestions - I can provide sample worksheets if my explanation is not adequate. I apologise I have no code yet to offer, as I am still in the very early stages of the design phase.
Some additional notes:
1) order numbers and product codes are numbers stored as text, case numbers are numbers stored as numbers.
2) Entries are unique. So if order1 and sku1 exist on tab 3 of workbook 2013, it will not exist anywhere else.
Thank you and kind regards
D.
this is my first post so apologies in advance if I am breaking any rules or if my post is not 100% informative.
Here is how the process is manually:
There are 3 spreadsheets. Master log, 2013, and 2014. Master logs comes from a finance-owned system, 2013 & 14 come from the employees of the warehouse.
All files contain a list of order numbers, product identifier numbers, and units quantity, amongst other information.
A member of our team has to compare if the information between master log and 2013/14 match.
Here is the process manually:
1) Open all 3 files.
2) Select master log.
3) Copy first order number.
4) Search on 2013 file (entire workbook) - if order number is found, read accross the line to check if product code is the same. If it is, check if case number is the same. If yes, write on master log "logged".
5) If product code is not the same, search next until found on either 2013 or 14.
6) If order and product code combo is found, but cases are different, write on master log "logged with case discrepancy".
7) If order and product code combo is not found, write on master log "not logged".
Then the "not logged" entries are sent on a separate spreadsheets.
I am wondering what is the best way to do it with my amateur vba skills.
First option:
Use vba to put a formula to a new comparisson column on the master log.
I am thinking a combination of index(match) formulas to return the number of cases, then compare the number of cases with what's already on the master log.
The problem I have with this option is that the formula must check a total of 10 tabs (5 on 2013 sheet, 5 on 2014 sheet), so it will be both hard to write and as a gut feeling, it sounds inefficient, as it has to check for two different cells (if cell1 = value1, then if cell3 = value2, then return cell4 etc).
When I did something similar on a different file, I used vba to put an extra column to do a concatenate so the index match would be easier, but I don't think its useful to do on 10 tabs.
Second option:
Use a macro that replicates what "search" does, then do a formula - however I am unsure on how to do this.
Third option?
I would appreciate your suggestions - I can provide sample worksheets if my explanation is not adequate. I apologise I have no code yet to offer, as I am still in the very early stages of the design phase.
Some additional notes:
1) order numbers and product codes are numbers stored as text, case numbers are numbers stored as numbers.
2) Entries are unique. So if order1 and sku1 exist on tab 3 of workbook 2013, it will not exist anywhere else.
Thank you and kind regards
D.
