Variation on duplicate values


New Member
Sep 19, 2014
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.

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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

                '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;
End Sub

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

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back