is there a macro that I can use to prepare bank rec?

[cindywang6135]

I need something that can identify one to many relationship. In the book, there are a lot of entries, and in the bank, it is only one lump sum. And also date is not matching. Thanks,

 

[Ben McClave]

Cindy,

I'm not sure from your description what you're looking for specifically. My guess is that you would like to be able to run a code that can look at a lot of small transactions to find out which ones add up to some larger transactions. If so, I don't think that macros are the way to go.

I used a similar approach to try to find four values that add up to one number, and even with a relatively small list of 50 potential values, the codecould take in excess of 15 minutes to run (each additional value increasedthe number of possible combinations exponentially).

The way I now try to figure these things out is to add a column next to thetransactions that I place an "X" in whenever I think a transaction is partof the grand total. Then, I use a SUMIF function to sum up all values where there is an "X" in the column. You could go a step further and have a target value in another cell and a variance check as well. Then, as you check off items, the SUMIF formula will update and your variance will begin to disappear.

It's not elegant, but without more information about your specific needs, it will be hard to provide much more.

Good luck,

Ben

 

[pleaseassist]

Ben

I believe I have a similar request. In preparing a bank reconciliation
I have two groups of data. Column A has amounts from my bank statement
Column B has amounts from my general ledger. There are numbers in bot
column that I can match up easily using vlookups. However, there may b
times when an amount in column A is the sum of multiple amounts fro
column B. Is there a way to do this?

Ideally, I would like some kind of program that could match u
everything and leave behind only what would be considered reconciliatio
items; however, I understand that excel may not have that capabilit
since I am dealing with about 500 lines in each column.

Thanks in advance

 

[Ben McClave]

Good morning,

I don't think that there is any good way to do this, as it is nearly impossible to know from amounts alone whether the transactions shown by the bank are truly matched up to the correct GL transactions in your books without some sort of unique identifier. For example, if you have a check number (even if there are multiple GL entries for that check) then you could match the sum of all amounts with that check number against the bank's total for that check.

Still, if you are looking for a code that will run through all of the permutations of a series of numbers, the macro below should do the trick. Please note the following limitations:

1. The macro will search for all combinations of up to 4 numbers that add to your target number and stop once reaching that target. This can take a LONG time for a long list of potential figures, so try to narrow down your list as much as possible before running.

2. Be sure to include up to 3 values of zero in your list of potential values. That way, if there are only 2 or 3 figures that total to your target, the zeroes will act as filler for the remaining numbers (i.e. if your target is $200.00 and you have transactions for $125.00 and $75.00, the macro will not find an answer unless there are also two zero values on the list).

3. This macro only searches a list of transactions to see if they add up toone target value exactly. This means that you will need to run this macrofor each unreconciled bank transaction against all unreconciled GL transactions. By the time you do this, it might have been quicker to manually reconcile the books.

4. The macro only searches through 4-number combinations, if there are 5 ormore transactions embedded within the bank's numbers, this macro will not catch them. You could tweak the macro to account for more numbers, but thecode will be much slower for each additional number you add.

5. The macro stops on the first solution it finds. If there are multiple potential solutions, the macro will not show them all. Thus, if you have a relatively common amount, you may find that the transactions highlighted are not correct.

As you can see, the limitations of this macro are many. If it is at all possible to use a different report as your source (for example an AR/AP report or a check register) that aggregates the data similarly to the bank, you may have more luck. For example, our office reconciles bank statements to our check register, which ties to our GL via our accounting software. Still, the macro has helped me find differences when all else has failed. For example, when I try to reconcile our books I sometimes find that I have a variance. This macro can help point me to the potential culprit. Hope this helps.

Ben

Sub FindMatches()
Dim cTarget As Currency 'Target amount
Dim lCol As Long 'Column number with potential values
Dim lRow As Long 'Last row number with potential values

Sheet1.Range("A:A").Interior.TintAndShade = 0
cTarget = Sheet1.Range("D1").Value
lCol = Sheet1.Range("A:A").Column
lRow = Sheet1.Range(Cells(50000, lCol).Address).End(xlUp).Row 'Number of rows in the column to check

Call HighlightParent(cTarget, lCol, lRow)

End Sub
Sub HighlightParent(lngSum As Currency, lngCol As Long, lngRows As Long)

Dim lngRow As Long
Dim lngTRow As Long
Dim lngURow As Long
Dim lngVRow As Long

For lngRow = 2 To lngRows
For lngTRow = lngRow To lngRows
For lngURow = lngRow To lngRows
For lngVRow = lngRow To lngRows

If Cells(lngRow, lngCol) + Cells(lngTRow, lngCol) + Cells(lngURow, lngCol) + Cells(lngVRow, lngCol) = lngSum _
And Cells(lngRow, lngCol).Address <> Cells(lngTRow, lngCol).Address _
And Cells(lngRow, lngCol).Address <> Cells(lngURow, lngCol).Address _
And Cells(lngRow, lngCol).Address <> Cells(lngVRow, lngCol).Address _
And Cells(lngTRow, lngCol).Address <> Cells(lngURow, lngCol).Address _
And Cells(lngTRow, lngCol).Address <> Cells(lngVRow, lngCol).Address _
And Cells(lngURow, lngCol).Address <> Cells(lngVRow, lngCol).Address Then

Cells(lngRow, lngCol).Interior.Color = vbYellow
Cells(lngTRow, lngCol).Interior.Color = vbYellow
Cells(lngURow, lngCol).Interior.Color = vbYellow
Cells(lngVRow, lngCol).Interior.Color = vbYellow

MsgBox "Success! "
Exit Sub
End If

'Uncomment below if you want to have the macro stop every so often to offera chance to exit
'If lngRow Mod 5 = 0 Then
'If MsgBox("No answer found yet. Would you like to continue searching?", 4) = vbYes Then
'continue
'Else
'Exit Sub
'End If
End If
Next
Next
Next
Next

MsgBox "No Matches. "

End Sub

 

[Living the Dream]

Hi

500 lines is a walk in the park so to speak considering the latest
versions of Excel have around a million rows...

What would make the whole process smoother would be to have another
column with some other type of reference, like an invoice number or similar.

I assume you are downloading your bank statement? does it allow you to
add or remove specific fields, and is it in some delimited form.

There should be a reference field which you could get anyone making
payment electronically to use this purely for the purpose of the
invoice/ reference number.

Then, in your accounting system, make sure the same Invoice number is
attached to that specific ledger entry.

you can then run a Find/Match using VB, anything that does not match
will highlight and you can then go and manually check why.

HTH
Mick.