BYU Student Author: @Jonathan_Weston
Reviewers: @Brett_Lowe, @Donovon
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
- Excel
Note: This challenge uses a VBA module to enable regular expressions that is not supported by Mac OS.
Need a program? Click here.
Overview
You are the new intern at Dunder Mifflin Paper Company, Inc. You want to go home and begin your relaxing weekend, but the evil HR representative, Toby, is holding you hostage. He has an Excel file filled with results from various training modules and quarterly reviews to calculate next year’s raise for each employee, but it is riddled with errors and bugs, and he wants you to fix it. You worry that he really could be the Scranton Strangler, so you decide to help him.
As soon as you agree, lightning strikes, and thunder booms. Toby has locked you in the Annex and won’t let you out until the job is done. To ensure that you do it right, Toby already counted all the errors and used those numbers to create a password that you must share with him to escape the annex. To create this password, you will use the number of each error type to assemble pieces of a regex statement that will extract the correct password from the “Scary Wall of Text” tab.
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”.
Instructions
Here are the rules of the game:
- In cells B2:B6, manually enter the count of each type of error. It may be helpful to highlight the cells containing errors and color code those highlights to the error types. Below you will find an explanation of each error.
- Hard Codes: An actual number was manually entered in the cell where a formula should be.
- #REF! Errors: The cell returns a #REF! Error.
- Formula Errors: The formula in the cell isn’t the correct formula, references the wrong cell, or does not reference the correct range.
- Missing Data: No data is contained in the cell. This error type does not include cells or rows that are supposed to be left blank.
- Wrong Data Type: numbers are treated as a string data type rather than numerical.
- In cells C2:C6, write an xlookup statement that will use the number of errors found for the error type, and return the corresponding regex statement segment. For example, if one error was found for Hard Codes, cell C2 should return “\$”
- In cell C9, concatenate all the regex segments in C2:C6 into a single regex statement.
- Use the regex statement created in C9 to extract the correct password from the wall of text in the “Scary Wall of Text” tab. To do this, you will use this formula “=RegExpExtract(text, regex)” in cell C12. FYI, RegExpExtract is a user defined function, it does not come built into Excel. This function is created as a VBA module that you can call upon, which this file enables you to do because it is a macro-enabled workbook.
- Present this password to Toby and he will release you.
Congratulations! You escaped the annex!
Data Files
Suggestions and Hints
- Here is a link to a website that has a regex cheat sheet and explains this and other user defined functions for regex in Excel.
- A great way to find bugs in your worksheet is to go to Formula Auditing under the Formulas tab.
- The “Show Formulas” button will show the actual formula in the cell, not the output. This is a good way to find cells that are hardcoded.
- The “Error Checking” button will go over each cell that doesn’t follow the patterns to the cells next to it. This is indicated by the green triangle in the top left corner of the cell.
Solution
