BYU Student Author: @Hyrum
Reviewers: @Jae, @Jake_Beckstead
Estimated Time to Solve: 15 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Congratulations on your internship at the Utah State Department of Commissions! Your boss has been very impressed with you during your first week and has noticed your technological inclination. With your background in accounting and your technological skill, your supervisor has assigned you the task of cleaning data and answering important questions related to budget analysis for Salt Lake City during the upcoming quarter. There is a lot of data available, but you need to clean it up and focus solely on Salt Lake City for the meeting. Don’t worry though! You only have to focus on the city, NAICS, and value of loan columns. A data dictionary is provided next to the excel file.
Clean up the data for Salt Lake City to answer some questions for your boss!
- In the ‘City’ column, change any variation of “Salt Lake City” to Salt Lake City, ex. NORTH SALT LAKE should become SALT LAKE CITY
- Remove any data that does not have a NAICS code or has a blank business type
- Using a Pivot Table, answer the following questions:
a. Which types of businesses are receiving the most and least loans in Salt Lake City? (Number of businesses with loans, not monetary value of loans)
b. Which NAICS code receives the most loans? (Number of businesses with loans, not monetary value of loans)
Suggestions and Hints
- Check for any abbreviations of Salt Lake City!
- Use a table to sort through the different names that could be used for Salt Lake City. (There are names like SLC and Sale lake city)
I spent way too much time on this, but I think I found a cool way to solve this. I wanted to make a way to automatically update the spreadsheet as I found more dirty data (i.e., alternatives of “Salt Lake City”). So here is what I did:
- Deleted the data that had missing values
- Created a lookup table in a different tab.
- Added a new column “City2” to the original data.
- In the “City2” column, I used an xlookup function to see if the City Name was contained in my lookup table. Turning it into a table allowed the formula to easily update so I wouldn’t have to continually change xlookup cell references if I added more data to my lookup table.
- Create my PivotTable…be careful you have to refresh your PivotTable anytime you add more data to your lookup table, or else it doesn’t work.
For those interested, here is my spreadsheet with the solutions:
Challenge17_DavidWood_Solution.xlsx (1.1 MB)
This was a quick and simple way to replace spelling variations in a table. I really liked it. I ended up making another Pivot table that used the NAICS description column to see which industries were receiving loans, not just the code. Most of the results were not what I expected.
Pivot Table Solution
Great challenge!! I made my second pivot table on NAICS description instead, but the result is the same.
To clean the data, I first got rid of the missing data then wrote a formula to fix all the dirty uses of Salt Lake City in a new column called “Cleaned City.” Here is my formula:
=IF(AND(ISERROR(FIND(“SALT”,E2)),ISERROR(FIND(“LAKE”,E2)),ISERROR(FIND(“SLC”,E2))),E2,“SALT LAKE CITY”)
Here is my solution file:
Challenge17_Solution.xlsx (1.1 MB)
Great challenge! In addition to the tables you created, I wanted to see Which businesses got loans in in the $5 -10 million. It seems that Limited Service Restaurants were the winners as shown in the screenshot below.
Here is my solution file:
Challenge17_PPP_Loans_To_Utah_Businesses.xlsx (1.1 MB)
This is seriously such a cool challenge. I really like @David_Wood’s idea of having a separate table to add any new name variations to. That’s probably the most robust way to clean the City name; I wish I had thought of it!
The only thing I did differently from anyone was using wildcard characters in the SEARCH() or FIND() functions. I feel like the wildcards aren’t nearly as well known by users at they should be. They’re super helpful–kind of like an SQL-lite–but you sometimes need to be careful that your wildcards aren’t overly inclusive, giving you data you don’t need.
At first I used SEARCH(“SAL? LAKE”,A2) since I noticed there was a typo in some of the entries (“SALE LAKE”). But after, I noticed there was also a “SLC” in the data, and I couldn’t find a way to make the wildcards work for all of the variations of Salt Lake while excluding everything else. Using SEARCH(“S*L*”,A2) is too inclusive and would change cities such as “Kaysville” or “Salina” (any city where an S preceeds an L), and using SEARCH(“S*L*C*”,A2) is too exclusive, not changing “North Salt Lake”, “Salt Lake”, etc.
Moral of the story–Excel wildcards are great, but can only get you so far! They definitely aren’t as robust as RegEx, but can be useful for simple text parsing. I’ve included my final formula that I used to clean the City below. It definitely isn’t as robust as using a separate table and lookups, but it worked for the data presented in this training!
=IF(IFERROR(SEARCH(“SAL? LAKE”,A2),0)>0,“SALT LAKE CITY”,IF(IFERROR(SEARCH(“SLC”,A2),0)>0,“SALT LAKE CITY”,A2))
This challenge was super fun!
I was able to make the required changed to the data using a simple table. I added only had to look through the possible values for a few seconds to find everything I thought was a variation of Salt Lake City and was able to change all of the values very easily from there. I also filtered out blanks in the other two fields. From there it was easy to create a PivotTable and find the answers!
I wish I had thought to use wildcard characters or an XLOOKUP like other people did. I ended up using the Find and Replace Feature on the home tab. Had to go through it a few times but it worked nicely!
This challenge was super helpful for a review on pivot tables! I thought it was fun to play around with the different filters
It’s not great but I’m glad to be done.
Challenge17_PPP_Loans_To_Utah_Businesses.csv (9.6 KB)
SpencerFlemingChallenge17_PPP_Loans_To_Utah_Businesses.xlsx (1.7 MB)
I really enjoyed learning about how to filter tables within excel and how to use equations to specify a specific text that I want read!
It was a lot of fun to solve. Here is my solution.
Jacob_Smith_Loans_To_Utah_Businesses.xlsx (1002.4 KB)
Simple and great review of pivot tables! I also learned some valuable methods to clean and filter tables within Excel. After completing the tasks listed in the instructions, I went to watch the solution video and found the “CTRL -” command to be really helpful and much simpler than the methods I employed.
Here is my solution to this challenge
0606_Skidmore_TrainingTechHubHomework3.xlsx (1012.7 KB)
The NAICS code that receives the most amount of loans is 541110!
I loved this challenge! It was great being reminded of how effectively Excel cleans data. I got 541110 for question 3b! I had a challenge getting the same answer for the first Pivot Table, but overall I enjoyed this project.
Challenge17_PPP_Loans_To_Utah_Businesses.csv (4.9 KB)
Here are my solutions! This was a great exercise and refresher for me specifically with the pivot tables!