Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    [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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    [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. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [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
    	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
    [MS MVP - Word]

  7. #7
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    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
  •