Challenge17_PPP_Loans_To_Utah_Businesses (1).xlsx (998.9 KB)
This was an awesome challenge. It was a good refresher on pivot tables!
3 Likes
1 Like
That was awesome! I’ve never used Pivot Tables. There were definitely different ways I cleaned up the data but I felt so powerful organizing the data to answer the questions. It makes me picture I get to do that for a boss and watch as their jaws drop! 
Challenge17_PPP_Loans_To_Utah_Businesses.csv (11.7 KB)
- To replace variations of Salt Lake to Salt Lake City, I used CTRL+H to FIND and REPLACE Salt Lake with Salt Lake City.
2.To removed data without NAICS code, I filtered the NAICS column to only show the “Blank” fields. Then I selected all and deleted them.
- finally, to get the answers in the pivot table, I summed the count of LoanRange and then alternatively added business type and NAICS description to find the answers
This was a nice challenge! It taught me a few new things I could do with excel!
Here is my file with solutions, it may not work as the file was throwing all sorts of errors at me.
To fix the variations in the spelling of Salt Lake City, I used the following formula in a column to the side:
=IF(ISERROR(SEARCH(“salt”,E2))=TRUE,E2,“SALT LAKE CITY”)
The formula searches for the word “salt” (not case sensitive) within the E column; if it is found, it outputs “SALT LAKE CITY” in the proper format. Otherwise, it defaults to whatever was originally in the E column. I then copied the results of this formula over the E column as values. Doing a final audit of the results of this method led me to see that there was one instance of a cell that misspelled the world “salt”, therefore not properly triggering the formula. I decided just to fix this one instance by hand.
To get rid of data with any blank values for NAICS code or Business Type, I simply filtered these columns for blanks and manually deleted the resulting rows.
I then made the pivot tables to find the solutions to questions 3a and 3b.
Time to complete: 15 minutes
Difficulty rate: beginner
The first thing I did was to use the find/replace tool to change all the variances of salt lakes city such as North salt lake, south salt lake and SLC to SALT LAKE CITY. Then I use If statement to quickly find missing data and delete rows.
After using pivot tables this are my results
Good challenge! Had fun doing it!
About 20ish minutes
I made the data a table and sorted through the filter drop down only selecting the values that were some variation of SLC. I selected those and corrected them while the sheet was filtered. I then filtered the NAICS and Business type by blanks and deleted those rows. I added a column to the table called ID. I then made a pivot table and put the Business type in the columns and ID as a value (but made it a count value). I did the same for NAICS and sorted the data to find the most and least # of loans.
Solution:
I got Corporation for the business type with the most Loans and a tie between ESOP and Self-Employed Individuals for the least type of loans. The NAICS code with the most loans was 722511.
Time to Complete: 15 minutes
Difficulty Rate: Beginner
I started by doing a find/replace on the City column to handle the Salt Lake City variants. I then removed the blanks from the NAICS and BusinessType columns. I then created two pivot tables, one to show the relationships between the number of loans and businesses in Salt Lake and the other to show the relationship between NAICS codes and they associated loans in Salt Lake City.
My data got very messy when I went though this but here is what I did.
- Select city column and do find and replace for all values related to “Salt Lake City” to make them say such. I found there was much variety in how SLC was inputted, so it took a few attempts.
- Select NAICS column, select dropdown arrow in the header and uncheck the blank option.
That should organize the data, but I did not get it organized well enough to answer the 3rd question accurately.
Time to complete 30 minutes
Difficulty intermediate
This was a great challenge, there were a few points that tripped me up and I had to spend more time figuring it out. I liked making the pivot table and I used count lenders as my value to figure out how many types of businesses are getting loans as well as how many NAICS are getting loans. I think what everyone else did on their Excel spreadsheets was more efficient and I am excited to try their solutions!
Answered the questions quickly and succinctly. Merely filtered and deleted the blank rows, changed the SaltLake City variants via the search function, and then created pivot tables that counted the respective critera.
Challenge17_PPP_Loans_To_Utah_Businesses-Complete.xlsx (1014.8 KB)
Time to complete: 20 minutes
Difficulty: Beginner
Really enjoyed this challenge- I found it to be very applicable to what I might see in the real world!
I couldn’t think of a way to do this initially and was impressed by the data filtering method you used. I had fun with the pivot tables once I caught on to your method of removing and cleaning the bad data.
Time to complete: 20 mins
Difficulty: Medium
Solution:
Challenge17_PPP_Loans_To_Utah_Businesses.xlsx (1014.3 KB)