Algorithm to pair positive and negative numbers and deal with residuals

[widdbar]

I have the following problem:

I have a number of data entries with following fields ID, Name, Category, Amount - currently in Excel columns. The aggregate of Amount will equal zero, ie there are positives and negatives - but it will not necessarily equal zero for each Category.

I need an algorithm which will pair off IDs in the following way:

• For each Category try and pair off within the category if possible.
• Create the minimum matches as possible - ie match an ID with Amount of +3, to one with -3 if possible. But if not, can split - so +1 of it could go to an ID of -1 and +2 to a different ID with -2.
• Repeat for all the categories and match any residuals across categories as needed

In the end I want a list showing the amount of each +ID going to each -ID.

Hope that makes sense!

 

[Rijnsent]

Hi Widdbar,
welcome to the forum. Could you add something like a screenshot? An example of what your input and desired output plus a show of your efforts so far would be great.
Thanks in advance,
Koen