Find which items make up a total

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

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

Reply | Quote

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.

Reply | Quote

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.

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

Reply | 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

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
	Range(Output).Offset(d, 0) = Addr(x, b) + "+" + Addr(y, b)
	d = d + 1
  Else
	For z = y + 1 To c
	If Cells(x, b) + Cells(y, b) + Cells(z, b) = Range(Target).Value Then
	  Range(Output).Offset(d, 0) = Addr(x, b) + "+" + Addr(y, b) + "+" + Addr(z, b)
	  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
  Addr = Cells(n, m).Address(False, False)
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.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

http://en.wikipedia.org/wiki/Greedy_algorithm

Reply | Quote

Thanks everyone, I think it’s in the “unsolvable in the lifetime of the universe” category.

Jim

Reply | Quote