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 to one target value exactly. This means that you will need to run this macro for 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 or more transactions embedded within the bank's numbers, this macro will not catch them. You could tweak the macro to account for more numbers, but the code 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 offer a 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