# Variation on duplicate values

#### mburgess

##### New Member
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:
Code:
``````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

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

Replies
1
Views
205
Replies
2
Views
160
Replies
12
Views
292
Replies
0
Views
199
Replies
1
Views
175

1,196,515
Messages
6,015,661
Members
441,914
Latest member
VBAllTheThings

### 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 MrExcel.com.

### Which adblocker are you using?

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

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
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