Dated: 06/27/18 Software Used: Excel 365, 2016, 2010
Recently, I was asked to assist with a system conversion. I was given the entire general ledger (G/L) from the old system in MS Excel as well as two worksheets for mapping the property numbers and account numbers to the new system. I’ve put a sample screen shot of the G/L so you can see the formating.
At around 26,000 line items, the lead accountant on the project considered using look up formulas. She couldn’t quite picture how to make that work with the format she had. ultimately the solution used will make use of look up formulas, in particular vlookup. But there is a step missing. The reason we can go right to look up formulas is because the general ledger is unnormalized.
Normalize Normalize Normalize
When they brought me in, I knew right away what we needed to do first. To borrow a term from Database Administration the G/L needs to be normalized. That is, it has to be recast it in a way that each row contains all the needed information for the vlookups. This G/L is grouped by Account Number. In other words, the account number doesn’t repeat for each line. To normalize it we have to put the account number on each row.
This was the plan I came up with:
Normalize the G/L
- Insert 2 Columns for the new property and account number
- Use vlookup to look up the new mapping
- Copy the needed info into a journal entry template
- Assign transaction numbers to the J/E
- Upload, post and ensure the T/B matches by period by property (her group handled this)
Code
I could have done the entire solution in VBA. But, time was an issue and this was the most direct approach that occurred to me with one viewing of the file.
This is a simple script to create. But first I’ll insert a column which will hold the account number. Next, I analyzed the structure of the G/L. Note that the lines that begin with a number are the account numbers. While lines that begin with a character are data lines. Using this information let’s build our program.
Option Explicit
Sub NormalizeGL()
Dim startRow As Long
Dim lastRow As Long
Dim accountNumber As String
Dim i As Long
startRow = 7
lastRow = Range("B1048576").End(xlUp).Row
For i = startRow To lastRow Step 1
If IsNumeric(Left(Range("B" & i).Value, 1)) Then
'row is an account number
accountNumber = Range("B" & i).Value
Else
'row is data line and needs the account number
Range("A" & i).Value = accountNumber
End If
Next i
End Sub
Running that will populate column A with the account numbers for each line. The code is intuitive. First I defined two variables to manage the length of the For loop: startRow and lastRow. The string variable accountNumber’s name is obvious. Last I defined the long variable i as our loop counter.
We assign the lastRow using a useful bit of code. What it does is it goes to the last physical row in that column and then searches upwards for a value. When it finds a cell with a value it assigns that row number to the variable. Many of my solutions use this exact fragment since it’s a very common task.
Next, we enter the For loop where the action happens. We examine the first character of each row. If that character is a number, then we assign the cell’s value to accountNumber. Otherwise we know it is a data row, so we’ll set the cell in column A to the value of accountNumber. The result looks like this:
After that, I sorted the normalized G/L by date and period. Then I removed the subtotals and useless artifacts of the report. Now it was a matter of using some simple vlookups to match the new property/account mapping. I inserted the mapping worksheets into the workbook. The mapping worksheets were simple two column (i.e. old and new) to make the vlookup easier. I’ve put an example of the vlookups below, but they are unremarkable.
=vlookup(B7,Mapping!$A$2:$B$4,2,FALSE)
=vlookup(A7,Mapping!$D$2:$E$3,2,FALSE)
I filled in each row with those formulas. Now each row has all the required information to create the journal entry. Again, for simplicity, I copied and pasted each column into the entry template. The normalized and mapped G/L looked like this:
Transaction Numbers
We’re missing one more piece. The journal entry needs transaction numbers for each group of entries. Normally I’d assigned these based on date. But in Yardi date and period are not related. You can use the same date in several periods. Indeed, this client had done this with audit entries and post period adjustments. We needed to combine the date and period into a unique value, which I call a hash. We can use the hash as a key to lookup a unique transaction number.
First I copied both columns to another worksheet. I used the Advanced Filter to remove the duplicates. If you’re unfamiliar with this:
From the Data menu, Under Sort & Filter click Advanced. It may you about not having header rows but this is unimportant for our task, you can ignore the warning.
Select the Range of dates and periods
Select the destination cells, I picked a column to the right
Check Unique records only
Click OK
You’ll see it created a new list of dates and periods with one line for each combination. In the adjacent column I added a formula to join the two into my hash value
=Concat(D2,E2)
or =CONCATENATE(D2,E2) depending on your version of Excel
then to the column to the right I put my transaction number (i.e. 1,2,3)
I created this hash value in one of the unused columns of the journal entry template. Then used a vlookup to find the transaction number. Since this template needs to upload as a csv file, I pasted the transaction numbers as values and then removed the hash column.
The entry was then uploaded and validated with no errors. This project took me about an hour an a half, but saved them one hundred hours of manual entry. You’ll note that I didn’t create a full VBA solution for this. While it is possible, I felt this was the quickest way with the fewest question marks for accuracy.
Do you want to get started with VBA? Or want to learn what a FOR loop is?
My book, Beginning Microsoft Excel VBA Programming for Accountants has many examples like this to teach you to use Excel to maximize your productivity! It’s available on Amazon, Apple iBooks and other eBook retailers!
Questions or Comments?
Connect with me:
Facebook https://www.facebook.com/DerekHarlanWriter/
Twitter https://twitter.com/tazbirdinvader
[…] Excel EOMONTH Function Leads to Consistent Expense Accruals […]