## Excel udf: Find positive and negative amounts that net to zero

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:**

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

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

### Download excel sample file for this tutorial.

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

### 5 Responses to “Excel udf: Find positive and negative amounts that net to zero”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Thanks for covering this topic (I've been looking for something like this for six months and had just about given up hope). Unfortunately, I'm having implementing it. Am I just supposed to have my amounts entered in column B, and then only to follow the "How to use user defined function in Excel", or do I have to also enter the formulas also. If so, in what cell do I enter =FIND_num(B2:B21, E21,5) + CTRL + SHIFT + ENTER ? Do I also need to enter any formulas in any other cells (if so, what do I enter and which cell(s) do I enter it. Thanks!

Mike,

You enter the =FIND_num(B2:B21, E21,5) in your cell range.

Example

1. Select your cell range (C23:V55 in the example above)

2. Paste user defined function the in formula bar

3. Press and hold Ctrl + Shift

4. Press Enter

=Find_num(cellrange, sum, range)

See the explaining picture above.

Thanks for the quick response, Oscar. I pressed alt-F11 to open vba editor, clicked Module on the Insert menu, copy and pasted the udf, exited vba editor, selected cell range of C23:V55, pasted the udf function in the formla bar, pressed and held Ctrl + Shift, and pressed enter. The function ran (which was very exciting!) and the results showed in columns C24 thru V55, but there weren't any results in column W or X. Please advise. Thanks.

Mike,

I added formulas in colummns W and X to show that the sum is in the specified range (-5 to 5) and how many numbers were found.

=FIND_num(B2:B21, E21, 5)

B2:B21 - Numbers

Cell E21 contains the sum value

5 is the range

Hello Oscar,

it is great tool you presented. I need to search inbetween of plenty items (1000 and more lines) I tried to exted formulas and matrix in file however when I try with more than 25 items I receive #VALUE error as an example. Do you have any idea what that is?

Thank you in advance for help.