Small Business looking for Excel/VB Macro Solution to bank rec . . .

[as47]

Background:
I am an accountant in a small business. We usually have 18 pages of activity on our bank statement each month. We get the bank statement information electronically (online) and we have our book information in electronic format (in Excel).

Issue:
After we put them both together into an Excel Worksheet, it takes one person about 6 or 7 hours to manually match deposits on the bank statement with deposits listed on our books. There has to be a faster way.

Question:
Can someone point me in the right direction to find a somewhat simple solution, or is someone willing to take this on as a pet project? I am sure a tool like this would be useful to hundreds of small businesses.

Current Situation:
I have the bank deposits in one column (with deposit amounts and dates in the adjacent columns) and the book deposits and dates in similar fashion on the same worksheet. I then use a series of VLookUps to find the book match and display it and it's date next to each bank deposit. Although this is faster than manually matching, I'd like to automate the matching process with a macro if possible. I'd like to be able to run a macro that looks at the bank deposits and finds matches in the book deposits.

Any thoughts?

 

[bryanbayfield]

Personally I'd use Excel to generate a transaction number for each day then I'd open both Excel files in Access and do a simple query on them using multiple field primary keys: Transaction Number, Amount, Date.

Say Column A is date, column B is deposit amount. Sort by date and deposit amount. Insert a column to the left of A. Assuming your first row contains column headers then ...

' Simple macro to generate transaction number:
Sub trans_num()
Dim i As Long
' reset used range
ActiveSheet.UsedRange
' arrays to hold transaction numbers and dates
Dim date_arr As Variant, trans_arr As Variant
' reading in date values
date_arr = ActiveSheet.UsedRange.Columns("A").Value
making transaction array same size as date array
ReDim trans_arr(1 To UBound(date_arr, 1), 1 To 1)
' first two entries for transactions are known - column header and day 1
trans_arr(1, 1) = "Transaction Number"
trans_arr(2, 1) = 1
' looping throuygh the rest of teh dates
For i = 3 To UBound(date_arr, 1)
       Select Case date_arr(i, 1) - date_arr(i - 1, 1)
           ' date examined is teh same as the previous, so increment transaction number by one
           Case 0
               trans_arr(i, 1) = trans_arr(i - 1, 1) + 1
           ' new date so reset transaction number
           Case Else
               trans_arr(i, 1) = 1
           End Select
        Next
' reading transaction number into Excel column A
ActiveSheet.UsedRange.Columns("A").Offset(0, -1).Value = trans_arr
End Sub

 

[as47]

Wow! Thank you for your input!