BYU Student Author: @Nate
Reviewers: @Erick_Sizilio
Estimated Time to Solve: 70 Minutes
We provide the solution to this challenge using:
- Excel
- VBA
Need a program? Click here.
DISCLAIMER
VBA macros may be malicious. The official challenge solutions below is safe, but be sure you completely understand the code posted in any comments before copying it to your computer. Report suspicious code.
Overview
You just started a job as the financial manager for Wasatch Dental, a growing dental practice based in Salt Lake City. Your boss, Dr. Hansen, is a great dentist—but not a great accountant. Since he started his practice in 1998, he has poured over paper bank statements and used rough estimates to gauge the practice’s profitability. In the past 2 years, he has constructed 3 additional offices in Denver, Seattle, and San Francisco.
With the new offices now up and running, Dr. Hansen has hired you to evaluate the profitability of each, starting with the first quarter of 2022. He downloaded statements from the company bank account and each of the office credit cards for you to use. Unfortunately, the data need to be cleaned up and classified first.
Instructions
The Excel file Dr. Hansen gave you has the following sheets:
- Bank Statement
- Bank deposits from 1/1/22 to 3/31/22
- All offices use one common account with Chase Bank to make deposits. Each transaction has an 8-digit office ID included in its line-detail (see the table for “Other Key Data” in the data dictionary below), and the only transactions in the Q1 2022 statement are deposits.
- Card 2251, Card 6738, Card 0956, & Card 5480
- Credit card statements from 1/1/22 to 3/31/22
- Each office has a single credit card used to make all purchases for that office (see the table for “Other Key Data” in the data dictionary below). Since Wasatch Dental uses cash accounting, you can assume that supplies and dental equipment are expensed when purchased.
- Approved Vendors
- This sheet contains the list of all approved vendors for each office. It also lists which account purchases from that vendor should be put in.
- Transaction Report
- Dr. Hansen wants your final report to be in a specific format, as shown in this sheet (see the data dictionary below for further detail).
Your job is to use any combination of VBA scripts and Excel formulas to get Wasatch Dental’s data into a uniform format that includes each transaction’s date, amount, vendor, associated account, and office (as shown in the “Transaction Report” sheet).
Suggestions and Hints
- Each vendor’s name should be found in the line description for each transaction. You’ll need to extract each name from any dates, transaction IDs, or other descriptors included in the line description.
- Each type of statement has different spacing between columns and rows. Not all follow a uniform format, so be sure to understand each type of statement before writing any code to extract data.
- Keep in mind that sometimes an Excel formula works quicker and more efficiently than VBA code.
Data Files
Solution
Solution Code
Sub clean()
'Declare variables
Dim fecha As String
Dim amount As Currency
Dim vendor As String
Dim office As String
Dim sheet_count As Byte
Dim row As Integer
Dim trans As Integer
Dim last_row As Integer
'Save offices into array
Dim offices() As Variant: offices = Array("SLC", "DEN", "SFO", "SEA")
'Save the columns and row offsets of date, vendor, and amount for each statement
Dim bank_statement(2, 1) As Integer
bank_statement(0, 0) = 2: bank_statement(0, 1) = 0 'DATE
bank_statement(1, 0) = 3: bank_statement(1, 1) = 2 'VENDOR
bank_statement(2, 0) = 4: bank_statement(2, 1) = 0 'AMOUNT
Dim amex_card(2, 1) As Integer
amex_card(0, 0) = 8: amex_card(0, 1) = 1 'DATE
amex_card(1, 0) = 5: amex_card(1, 1) = 0 'VENDOR
amex_card(2, 0) = 3: amex_card(2, 1) = 0 'AMOUNT
Dim visa_card(2, 1) As Integer
visa_card(0, 0) = 4: visa_card(0, 1) = 0 'DATE
visa_card(1, 0) = 10: visa_card(1, 1) = 0 'VENDOR
visa_card(2, 0) = 13: visa_card(2, 1) = 0 'AMOUNT
'Create array of sheet names and statement type
Dim statements(4, 1) As Variant
statements(0, 0) = "Bank Statement"
statements(0, 1) = bank_statement
statements(1, 0) = "Card 2251"
statements(1, 1) = amex_card
statements(2, 0) = "Card 6738"
statements(2, 1) = visa_card
statements(3, 0) = "Card 0956"
statements(3, 1) = amex_card
statements(4, 0) = "Card 5480"
statements(4, 1) = visa_card
'initialize transactions
trans = 2
'loop through sheets in 'statements' array
For sheet_count = 0 To 4
'find last row with data in sheet
last_row = Sheets(statements(sheet_count, 0)).Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).row
'extract data from statement and add to 'Transaction Report'
For row = 6 To last_row
'checks for non-empty cells
If Not IsEmpty(Sheets(statements(sheet_count, 0)).Cells(row + statements(sheet_count, 1)(0, 1), statements(sheet_count, 1)(0, 0)).Value) Then
'saves date
fecha = Sheets(statements(sheet_count, 0)).Cells(row + statements(sheet_count, 1)(0, 1), statements(sheet_count, 1)(0, 0)).Value
'for visa transactions, adds year to date
If (statements(sheet_count, 0) = "Card 6738") Or (statements(sheet_count, 0) = "Card 5480") Then
fecha = fecha + "/22"
End If
'saves vendor
vendor = Sheets(statements(sheet_count, 0)).Cells(row + statements(sheet_count, 1)(1, 1), statements(sheet_count, 1)(1, 0)).Value
If statements(sheet_count, 0) = "Bank Statement" Then
office = Right(vendor, 8)
vendor = Replace(vendor, Left(vendor, 6), "")
vendor = Replace(vendor, Right(vendor, 11), "")
ElseIf (statements(sheet_count, 0) = "Card 6738") Or (statements(sheet_count, 0) = "Card 5480") Then
vendor = Split(vendor, " - ")(0)
Else
vendor = Replace(vendor, Left(vendor, 11), "")
End If
'saves amount
amount = Sheets(statements(sheet_count, 0)).Cells(row + statements(sheet_count, 1)(2, 1), statements(sheet_count, 1)(2, 0)).Value
'inputs date, vendor, andamount into 'Transaction Report'
Sheets("Transaction Report").Cells(trans, 1) = fecha
Sheets("Transaction Report").Cells(trans, 2) = amount
Sheets("Transaction Report").Cells(trans, 3) = vendor
'for card transactions, adds office to 'Transaction Report'
If sheet_count > 0 Then
Sheets("Transaction Report").Cells(trans, 6) = offices(sheet_count - 1)
Else
'Inputs office for revenue transactions
If office = "83675291" Then
Sheets("Transaction Report").Cells(trans, 6) = offices(0)
ElseIf office = "40928764" Then
Sheets("Transaction Report").Cells(trans, 6) = offices(1)
ElseIf office = "57263148" Then
Sheets("Transaction Report").Cells(trans, 6) = offices(2)
Else
Sheets("Transaction Report").Cells(trans, 6) = offices(3)
End If
End If
trans = trans + 1
End If
DoEvents
Next
DoEvents
Next
End Sub
Challenge113_Solution.xlsx
Solution Video: Challenge 113|EXCEL&VBA – The Data Dentist