# Thread: Find which items make up a total

1. Hi,

I have a list of cheque amounts (1000 or so), a combination of these amounts total to £1,781,666.37.

I know that some of the amounts (700 or so) are definitely included in the total, so I therefore have a balance of around 300 amounts that total exactly £385,896.21.

Does anyone know of a source of code that can work out which cheques are in that total. I'm aware that there may be more than one answer using this method.

Thanks
Jim

2. You may be able to use the Solver add-in for this, although I don't know whether it can handle such a large number of values. See Reconciling variable data amounts to a variable given total.

3. [quote name='HansV' post='778769' date='08-Jun-2009 13:03']You may be able to use the Solver add-in for this, although I don't know whether it can handle such a large number of values. See Reconciling variable data amounts to a variable given total.[/quote]

I'm getting a "Too many adjustable cells" error.

I was hoping for a piece of anagram style code, that would enter 1s & 0s in every possible combination till it found a solution.

Jim

4. The number of combinations would be much too large - the lifetime of the universe is too short...
You may need commercial reconciliation software, but I have no experience with that.

5. [quote name='HansV' post='778778' date='08-Jun-2009 14:00']The number of combinations would be much too large - the lifetime of the universe is too short...
You may need commercial reconciliation software, but I have no experience with that.[/quote]
Oh well, it was worth a try.

I think I might try some kind of random generator & leave it running for a few weeks!

Thanks,
Jim

6. [quote name='jmacleod' post='778941' date='09-Jun-2009 19:01']Oh well, it was worth a try.

I think I might try some kind of random generator & leave it running for a few weeks!

Thanks,
Jim[/quote]
Hi Jim,

The following macro (which I found somewhere a long time ago ... ) checks all cell pairs and triplets in the selected rows and reports any that add up to the value stored in the nominated target cell:
Code:
```Sub FindSubSets()
Application.ScreenUpdating = False
Dim a As Long, b As Long, c As Long, d As Long
Dim Target As String, Output As String
Dim x As Long, y As Long, z As Long
If Selection.Rows.Count = 1 Or Selection.Columns.Count <> 1 Then
MsgBox "Please select more than one row in a single column"
Exit Sub
End If
a = ActiveCell.Row
b = ActiveCell.Column
c = Selection.Rows.Count + ActiveCell.Row - 1
d = 0
Target = InputBox("What is the address of the cell" & vbCrLf & "you want the numbers to add up to?")
Output = InputBox("What is the address of the first cell" & vbCrLf & "you want to output the results in?")
On Error GoTo Abort
Range(Output).Offset(d, 0) = ""
For x = a To c
For y = x + 1 To c
If Cells(x, b) + Cells(y, b) = Range(Target).Value Then
d = d + 1
Else
For z = y + 1 To c
If Cells(x, b) + Cells(y, b) + Cells(z, b) = Range(Target).Value Then
d = d + 1
End If
Next z
End If
Next y
Next x
Range(Output).Offset(d, 0) = ""
Abort:
Application.ScreenUpdating = False
End Sub

Private Function Addr(ByVal n As Integer, ByVal m As Integer) As String
End Function```
If you need quartets etc, the above code should serve as a good starting point, but note that execution time grows exponentially as you increase the size of the range and/or the number of combinations.

7. Thanks everyone, I think it's in the "unsolvable in the lifetime of the universe" category.

Jim

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•