I created/modified an udf to solve his question: Find positive and negative amounts that net to zero in excel

Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero and clear those items

Criteria:

• Minimze the number of open items
• include as many transactions as possible
• net as close to zero as possible

Udf (array) in cell range c23:V55:

=FIND_num(B2:B21, E21, 5) + CTRL + SHIFT + ENTER.

The udf processes these 20 numbers in around 30 seconds. There are 18 numbers found in row 24 and also in row 25 and 31.

Formula in column W sums values in each particular row.

Formula in column X counts values.

### How to use user defined function in excel

1. Press Alt-F11 to open visual basic editor
2. Click Module on the Insert menu
3. Copy and paste the belowuser defined function
4. Exit visual basic editor

Find amounts netting to zero.xls
(Excel 97-2003 Workbook *.xls)

VBA code

```Function Find_num(rng As Range, cell As Range, num_range As Single)
Dim com() As Single, c As Single, i As Single
Dim s As Single, d As Single, u As Single, v As Single
Dim arr() As String, r As Single, p As Single, t As Single
Dim sum_cells As Single, j As Single, k As Single, l As Single
sum_cells = Application.WorksheetFunction.Sum(rng)
c = rng.Rows.Count
ReDim arr(1 To 10000, 1 To c)
r = 1
For i = 0 To Int(c / 2)
If i <> Int(c / 2) - 1 Then
t = WorksheetFunction.Combin(c, i + 1)
Else
t = WorksheetFunction.Combin(c, i + 1) / 2
End If
ReDim com(i)
For j = 0 To i
com(j) = j + 1
Next
k = i
For s = 1 To t
If com(k) > c And i > 0 Then
p = 0
Do Until com(k) <= c - p
com(k - 1) = com(k - 1) + 1
k = k - 1
p = p + 1
Loop
Do Until k >= i
k = k + 1
com(k) = com(k - 1) + 1
Loop
End If
d = 0
For j = 0 To i
d = d + rng(com(j))
Next j
If d <= cell + num_range And d >= cell - num_range Then
For j = 1 To i + 1
arr(r, j) = rng(com(j - 1))
Next j
r = r + 1
End If
If sum_cells - d <= cell + num_range And sum_cells - d >= cell - num_range Then
For j = 1 To c
v = 0
For u = 0 To i
If rng(com(u)) = rng(j) Then v = 1
Next u
If v = 0 Then
arr(r, j) = rng(j)
End If
Next j
r = r + 1
End If
com(k) = com(k) + 1
Next s
Next i
Find_num = arr()
End Function
```