BYU Student Author: @Jonathan_Weston
Reviewers: @DylanKing, @Marco
Estimated Time to Solve: 60 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
This is the first part of the Inventory Check Challenge. Click here to participate in the challenge Inventory Check Part 2.
Overview
You are the lead auditor for Innovatech Publishing. Currently, you are leading the inventory count for all the items listed in their Raw Materials, Work in Process, and Finished Goods balances. Luckily, every single item is tracked using an ISBN barcode to help you with your audit. Several staff members have already conducted the count and have compiled all the information into an excel file. As the lead auditor, your task is to review this file to make sure that all the information is accurate and free from error. You’ve decided that regex statements will be the best way to verify the accuracy of the information.
Unfortunately, Excel does not provide any formulas that use regex statements. So, you’ll need to create a user function in VBA in order to utilize the power of regular expressions for this analysis. Since this is a regex challenge, not a VBA challenge, the user defined function has already been created and loaded into the module of your .xlsm file. If you would like to see what it looks like, open the VBA window in the Developer tab and look under the Module of the file.
The Excel file for this challenge is a macro-enabled workbook. It’s possible that your computer will only let you access the file in View mode. To unblock this file, go to your file explorer, right-click on the file, click properties, and then click “un-block”.
Since macro-enabled workbooks can contain malware, please view the code before running the programs, and hesitate to download macro-enabled files that are not posted directly from TechHub.Training.
Instructions
Columns A-E contain values collected by the audit staff from the inventory count. Columns F-J are where you will use the RegExpMatch formula. Column M is where you will write the regex statements. The two arguments for the RegExpMatch formula are 1) the string you are analyzing and 2) the regex statement.
Write a regex statement that attempts to match a certain pattern for each column. Most (if not all) of the RegExpMatch formulas should return TRUE. If not, it means that the value is not consistent with what is expected from the audit and will need to be followed up with the client.
- Product_ID: Each product has an ID number that will start with three letters to identify which department balance it pertains to (RAW for Raw materials, WIP for Work in Process, and FIN for Finished Goods). The three letters are then followed by a dash and three numbers. Here is an example for an item in the Work in Process balance: “WIP-005”.
- Rec_Date: The Rec_Date refers to when the item was received into the department. This audit is for the first three months of the year 2016. According to Innovatech’s operations flowcharts, items should have only been received in January, February, or March.
- ISBN: This is the acronym for the International Standard Book Number. It is a 13-digit number that identifies a specific book, edition, or book-like product. Every ISBN consists of thirteen digits and whenever it is printed it is preceded by the letters ISBN. The thirteen-digit number is divided into four parts of variable length, each part separated by a hyphen. The last segment will always be a single digit. If there are a total of 17 characters that follow the “ISBN“, the first three digits will be “978”, if not the first three digits will be “979”. The regex statement to match the ISBN is very complex and is considered optional for this challenge. Under Solutions, you can find the regex statement for this column along with the others.
- Count_Date: This refers to when the inventory was counted for the audit. The count must have been conducted during the month of march.
- Support_Filepath_Ext: Your staff have all uploaded pictures of the ISBN barcodes into your shared folder for the client audit. The entire filepath is not included, just where the shared folder begins. The first two folders should be “Innovatech Publishing” > “Inventory_Check”. The next folder should be the corresponding department balance (RAW, WIP, FIN). The file should be named “barcode” followed by the ISBN number.png. You will not need regex to analyze this column. You can instead use concatenation statements and set them equal to this column to see if they match.
Once you have created all of the RegExpMatch formulas, add conditional formatting to easily identify the values that returned “FALSE” to the match. These are the items that you should verify with your staff.
Data FIles
Suggestions and Hints
Here is a website that has a regex cheat sheet and explains the VBA function.
If you get stuck on the regex statements, you can find them under Solution
Solution
These are the regex statements for the first four columns. The Support_Filepath_Ext can be matched by concatenating the first half of the filepath with the Product_ID and ISBN
Product_ID Check Regex
(RAW|WIP|FIN)-\d{3}
Rec_Date Check Regex
[1-3]/(0?[1-9]|[12][0-9]|3[0-1])/2016
ISBN Check Regex
ISBN\s(?=.{17}$)97(?:8|9)(-)\d{1,5}\1\d{1,7}\1\d{1,6}\1\d
Count_Date Check Regex
3/(0[1-9]|[12][0-9]|3[0-1])/2016
Support Filepath Ext concat
This is the formula for cell J2:
=E2=CONCAT("\Innovatech Publishing\Inventory_Check\",LEFT(A2,3),"\barcode ",C2,".png")
Challenge41_Solution.xlsm
Solution Video: Challenge 41|EXCEL – Inventory Check
