Remove records based on pairing / count

lizhaozhi

I have a table that looks like this:

Table 1

Numbers	Sources
5000	File A
2000	File A
-200	File A
200	File B
-5000	File B
-2000	File B
200	File B

What I want is to pair the rows in Files A and B where each pair sums to 0. For example the 5000 from File A on row 1 is paired with -5000 from File B on row 5. And 2000 from File A on row 2 is paired with -2000 from File B on row 6, etc.

The rule is that the number of negatives and positives from the two files should match. Therefore, in the above example, either the 200 from File B on row 4 or row 7 should be removed because there's only one -200 from File A. This rule shall apply to all input files like these. There must be the same number of positive and the same number of negative, each from a different file. The only two files possible are Files A and B.

I've tried many methods but couldn't find a way to solve this. Please note that only solutions that work for all scenarios would be accepted. Please do not hardcode this input file for the desired outcome. The SAME workflow should be tested with other scenarios as well such as below:

Table 2

Numbers	Sources
5000	File A
-2000	File A
-200	File A
200	File B
-5000	File B
-2000	File B

In this case, the -2000 from File A and -2000 from File B do not sum to 0, hence, both should be removed.

Table 3

Numbers	Sources
5000	File A
2000	File A
-200	File A
200	File B
-5000	File B
-2000	File B

In this case, all records shall be kept.

Table 4

Numbers	Sources
5000	File A
-2000	File A
-2000	File A
2000	File B
-5000	File B
-2000	File B

In this case, one of the two -2000 from File A and the -2000 from File B should be removed.

Table 5

Numbers	Sources
5000	File A
5000	File A
-2000	File A
2000	File B
-5000	File B
-5000	File B

In this case, all records are kept.

I would appreciate your solution workflow as an attachment coupled with a little explanation.

Qiu

@45179902 
How about the pair 200 and -200 from your 3rd table?