Vlookup and Trial Balances Function and Example
A new spreadsheet called Book1.xls will be created. We then complete same procedures for 12/31/06, except that in the drop down we select "Book1.xls" instead of "(new book)." The result will be a new file with two trial balances in it:
Finally, we rename the tabs from "Step 2" and "Step 2 (2)" to "Step 2-05" and "Step 2-06" to indicate 12/31/05 and 12/31/06 trial balances respectively:
Lastly, we save the new file as ABC Company Trial Balances Combined.xls.
3) Create a complete list of trial balance accounts
If we knew for sure that there were no changes in the chart of accounts from one period to another, this exercise would be much easier. We would just copy and paste 12/31/06 balances along 12/31/05 trial balance and accounts would be aligned with no problems. However, if there were any changes in the chart of accounts, the accounts will not match side by side. Changes may relate to eliminating existing or adding new accounts. In our example, three existing accounts (8011, 8013 and 8014) were eliminated and two new accounts (8022 and 8023) were added during 2006.
We need to compile a list of all possible account numbers and descriptions before we can proceed. To do this, we copy and paste all accounts from 12/31/05 trial balance into a new tab in the combined file. We then copy and paste all accounts from 12/31/2006 trial balance below the list copied from 12/21/05 trial balance. The print screen below shows the result of two copy and paste operations:
Note that the total number of records, including one header (row 1) is 108.
Now we can create a list of unique accounts without duplicates. We use Advanced Filter from menu Data > Filter to do that. Go to Data, then Filter and select Advanced Filter:
Page 3 of 7
Location