Summing to zero

[TallyHo]

I am working with a large column of debits and credits (D/C), I want to find the D/C that offset each other. the problem is that x number of credits may offset y number of debits. Is this possible to do in Excel? If so, how?

In this list items marked A sum to zero.


(1,343,683.46) A
(777,341.06)
(507,512.43)
(103,533.13)
[96,782.07)
(7,026.98) A
(5,862.35)
n
n
n
130,422.47
221,773.74 A
222,245.96 A
269,795.66
486,485.44
604,327.47
906,690.74 A
n
n
n
n
n

 

[inarbeth]

Hi welcome to the board.
I think your problem is a mathematical one rather than an Excel one. There may not be one unique solution. For example with the list
2
(2)
5
(3)
(5)

you could net off 2 and (2) or 5 with (2) and (3) or 5 with (5)

With a larger range of numbers the possibilities increase and you need some rules to decide on pairings. In my simple example the second and third are mutually exclusive.

 

[schwede]

I would agree with inarbeth but there could be a way to work that out depending on a few conditions:

1. Do your total credits equal total debits?
2. Is there at least one "match" for each item?

If the answer is yes in both cases, I would basically do something like this:
1. create all possible sums of all elements for both sides.
2. find all possible matches.
3 in order to avoid the problem described by inarbeth, go through the credits or debits side (shouldn't really matter which) and write all those sums where any element only appears once into a summary sheet and delete these elements from the original list.
4. repeat steps 1-3 until there are no more elements left that only appear in one sum
If you have any elements left after that your in trouble if you don't have any additional information.

Cheers,
Schwede

 

[TallyHo]

May Be Impossible

Since my list does not net to zero and there may be up to 250 items with matching amounts of 2 to 6 items the possible combinatioms are huge.

Thank you for your help though, very much appreciated.

 

[schwede]

I'm not even sure the lists need to net to zero. You could just create a difference-element at the end to force a zero net.

Do you mean up to 250 debit and 250 credit items? Or 250 in total?

Of course, the code would take some time to run but if it has to be done it has to be done.

 

[TallyHo]

250 items in total

Any unmatched debits or credits need to be investigated, I am trying to eliminate the matched items to reduce the amount of investigations needed.

 

[just_jon]

A while back there was a Challenge of the Month that was similar.

It is here.

It is a far more complex task than you might imagine, as can be seen by taking a gander at the code.