Looking to match transactions, in order to work out variances seperately

Ok so this is really hard to explain what I am trying to do here but I'll give it a shot.

ss1.PNG

What I am trying to do here is match each of these values (Amount in doc. curr.) whether they are a plus or a minus, and give them a unique identifier (Transaction Number) if the values are the same. Then these values would be added together on the basis of their identifier seperately, like below.

ss2.PNG

The idea here is that the transactions are linked if they equal each other (or a reversal of the value) and if I can seperate them like the image above, I can see where there are discrepancies. Is there a formula that will carry this function out? Essentially, I'm looking for the formula to be within the "Transaction Number" column.

Any help here would be greatly appreciated, as there are some with hundreds of transactions and it would take an age to match them manually.

Many thanks!

Unless you post an Excel sheet, it is unlikely that you will get a working solution.

Assuming your transaction number heading is in A1 in A5 write:

Formula:

Please Login or Register  to view this content.

in A6 array formula (commit with Ctrl+Shift+Enter not just Enter):

Formula:

Please Login or Register  to view this content.

and copy down

once you have assigned transaction number, summing can be done with either Pivot Table or simple SUMIF