Variation on duplicate values

[mburgess]

I think this is a really simple problem and I am just overlooking the solution, a nudge in the right direction would be greatly appreciated.
I am working with a series of accounts that have multiple debits cleared out by a single credit. How I can make excel flag the multiple debits that would equal the one credit and just leave me with the ones that aren't cleared out. The values are all numbers greater than 100,000.00 that go down to the penny so I am confident if would not falsely exclude ones that aren't cleared out, if need be I can constrain the timeframe to eliminate this potential issue even further.

Thanks in advance for any help.

 

[pbornemeier]

I would never discount the possiblity of multiple solutions. If there were 3 debits for 1000.00 there would be no way to tell which one (or ones) should be associated with a particular credit.

This code should help: vba - which combinations of numbers in a set add up to a given total - Stack Overflow

Code copied below:

Option Explicit
Sub Test_AllSumsForTotalFromSet()
    Dim numberSet, total As Long, result As Collection

    numberSet = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    total = 40

    Set result = GetAllSumsForTotalFromSet(total, numberSet)

    Debug.Print "Possible sums: " & result.Count

    PrintResult result
End Sub

Function GetAllSumsForTotalFromSet(total As Long, ByRef numberSet As Variant) As Collection
    Set GetAllSumsForTotalFromSet = New Collection
    Dim partialSolution(1 To 1) As Long

    Set GetAllSumsForTotalFromSet = AllSumsForTotalFromSet(total, numberSet, UBound(numberSet), partialSolution)
End Function

Function AllSumsForTotalFromSet(total As Long, ByRef numberSet As Variant, numberSetIndex As Long, ByRef partialSolution() As Long) As Collection
    Dim index As Long, number As Long, result As Collection

    Set AllSumsForTotalFromSet = New Collection

    'break if numberSetIndex is too small
    If numberSetIndex < LBound(numberSet) Then Exit Function

    For index = numberSetIndex To LBound(numberSet) Step -1
        number = numberSet(index)

        If number <= total Then
            'append the number to the partial solution
            partialSolution(UBound(partialSolution)) = number

            If number = total Then
                AllSumsForTotalFromSet.Add partialSolution

            Else
                'Set result = AllSumsForTotalFromSet(total - number, numberSet, index, CopyAndReDimPlus1(partialSolution)) 'repeat an input number
                Set result = AllSumsForTotalFromSet(total - number, numberSet, index - 1, CopyAndReDimPlus1(partialSolution)) 'no repeats
                AppendCollection AllSumsForTotalFromSet, result
            End If
        End If
    Next index
End Function

'copy the passed array and increase the copy's size by 1
Function CopyAndReDimPlus1(ByVal sourceArray As Variant) As Long()
    Dim i As Long, destArray() As Long
    ReDim destArray(LBound(sourceArray) To UBound(sourceArray) + 1)

    For i = LBound(sourceArray) To UBound(sourceArray)
        destArray(i) = sourceArray(i)
    Next i

    CopyAndReDimPlus1 = destArray
End Function

'append sourceCollection to destCollection
Sub AppendCollection(ByRef destCollection As Collection, ByRef sourceCollection As Collection)
    Dim e
    For Each e In sourceCollection
        destCollection.Add e
    Next e
End Sub

Sub PrintResult(ByRef result As Collection)
    Dim r, a

    For Each r In result
        For Each a In r
            Debug.Print a;
        Next
        Debug.Print
    Next
End Sub

Copied the code because I have seen too many dead links.