complicated bank rec - how can I add it up?

if you could email me a copy to andrew@tiekeyaccounts.co.uk I'd be extremely greatful.

I have answered my own question, and marked it as best answer. This is Aweb at its best! Thanks everyone for the fantastic posts in this thread,

I found it here http://www.tushar-mehta.com/excel/templates/match_values/index.html#VBA_multiple_combinations

As far as I can tell the code below is easy to understand and is also probably about as fast as one can get.  Put the code below in a standard module. (Press alt-f11 then click on "insert" on the menubar, then "module" then paste the code in, then exit back to excel

The data for the search should be organized in a single contiguous range in one column.

Select the range containing all the information (the cell indicating the maximum number of solutions, the target value, and all the values to be matched), and use ALT+F8 to run the startSearch procedure.

Option Explicit
Function RealEqual(A, B, Epsilon As Double)
RealEqual = Abs(A - B) <= Epsilon
End Function
Function ExtendRslt(CurrRslt, NewVal, Separator)
If CurrRslt = "" Then ExtendRslt = NewVal _
Else ExtendRslt = CurrRslt & Separator & NewVal
End Function
Sub recursiveMatch(ByVal MaxSoln As Integer, ByVal TargetVal, InArr(), _
ByVal CurrIdx As Integer, _
ByVal CurrTotal, ByVal Epsilon As Double, _
ByRef Rslt(), ByVal CurrRslt As String, ByVal Separator As String)
Dim I As Integer
For I = CurrIdx To UBound(InArr)
If RealEqual(CurrTotal + InArr(I), TargetVal, Epsilon) Then
Rslt(UBound(Rslt)) = (CurrTotal + InArr(I)) _
& Separator & Format(Now(), "hh:mm:ss") _
& Separator & ExtendRslt(CurrRslt, I, Separator)
If MaxSoln = 0 Then
If UBound(Rslt) Mod 100 = 0 Then Debug.Print UBound(Rslt) & "=" & Rslt(UBound(Rslt))
Else
If UBound(Rslt) >= MaxSoln Then Exit Sub
End If
ReDim Preserve Rslt(UBound(Rslt) + 1)
ElseIf CurrTotal + InArr(I) > TargetVal + Epsilon Then
ElseIf CurrIdx < UBound(InArr) Then
recursiveMatch MaxSoln, TargetVal, InArr(), I + 1, _
CurrTotal + InArr(I), Epsilon, Rslt(), _
ExtendRslt(CurrRslt, I, Separator), _
Separator
If MaxSoln <> 0 Then If UBound(Rslt) >= MaxSoln Then Exit Sub
Else
'we've run out of possible elements and we _
still don't have a match
End If
Next I
End Sub
Function ArrLen(Arr()) As Integer
On Error Resume Next
ArrLen = UBound(Arr) - LBound(Arr) + 1
End Function
Sub startSearch()
'The selection should be a single contiguous range in a single column. _
The first cell indicates the number of solutions wanted. Specify zero for all. _
The 2nd cell is the target value. _
The rest of the cells are the values available for matching. _
The output is in the column adjacent to the one containing the input data.
Dim TargetVal, Rslt(), InArr(), StartTime As Date, MaxSoln As Integer
StartTime = Now()
MaxSoln = Selection.Cells(1).Value
TargetVal = Selection.Cells(2).Value
InArr = Application.WorksheetFunction.Transpose( _
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Value)
ReDim Rslt(0)
recursiveMatch MaxSoln, TargetVal, InArr, LBound(InArr), 0, 0.00000001, _
Rslt, "", ", "
Rslt(UBound(Rslt)) = Format(Now, "hh:mm:ss")
ReDim Preserve Rslt(UBound(Rslt) + 1)
Rslt(UBound(Rslt)) = Format(StartTime, "hh:mm:ss")
Selection.Offset(0, 1).Resize(ArrLen(Rslt), 1).Value = _
Application.WorksheetFunction.Transpose(Rslt)
End Sub

More links for anyone who's interested...

http://www.mrexcel.com/pc09.shtml

http://groups.google.com/groups?as_q=match+numbers+sum+target&as_ugroup=*Excel*

from here http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/92454de3745e9b65/373651f2beb3f843?q=match+numbers+sum+target+group:*Excel*

One way but you need the solver add-in installed (it comes with
      excel/office,check under tools>add-ins)
      put the data set in let's say A2:A8, in B2:B8 put a set of ones {1,1,1 etc}
      in the adjacent cells
      in C2 put 8, in D2 put
      =SUMPRODUCT(A2:A7,B2:B7)
      select D2 and do tools>solver, set target cell $D$2 (should come up
      automatically if selected)
      Equal to a Value of 8, by changing cells $B$2:$B$7, click add under  Subject
      to the constraints of:
      in Cell reference put
      $B$2:$B$7
      from dropdown select Bin, click OK and click Solve, Keep solver solution
      and look at the table
      2         1
      4         0
      5         0
      6         1
      9         0
      13       0
      there you can see that 4 ones have been replaced by zeros and the adjacent
      cells to the 2 ones
      total 8

Takes ages for large amounts of data though