Netting off Transactions

[tsetford]

All,

Please can you assist me with the query below. 

I am trying to create an indicator to allow me to net off some positive and negative transactions.  The rule that needs to be applied is if transaction ref is the same as another transaction ref and the FCY of both those transactions and the LCY of both those transactions net off to zero then create an indicator saying "net" so i can then exclude them.  In my example rows 2 and 3 net off and 15 and 16 and 29 and 30 as they have the same reference and the fcy amount and lcy amounts added together equal 0.

thanks in advance

[atcodedog05]

Hi @tsetford 

Here is how you can do it.

Workflow:

1. Using formula tool to remove comma from [FCY] and [LCY]

2. Using select tool to convert [FCY] and [LCY] number.

3. Using multi-row formula tool to check if Ref is same and  [FCY] and [LCY] nets and flag it

Hope this helps : )

[tsetford]

Thank you this is great although I had hoped "net" would go across the two lines that netted off not just the one.  Is it possible to do that.

Many Thanks

[atcodedog05]

Hi @tsetford 

It is possible.

[atcodedog05 · Accepted solution]

Hi @tsetford 

Here is how you can do it.

Workflow:

Hope this helps : )

[tsetford]

Thank you very much that has worked brilliantly

[Vijay_Vittal_11]

This does not completely remove the netting off transactions. 

My Scenario.

30,000 lines of policy numbers with +ve and -ve amounts

EG:

ABCDEFGH123 $ 1.50

ABCDEFGH123 $ -1.50

ABCDEFGH123 $ 2.50

ABCDEFGH123 $ -2.50

ABCDEFGH123 $ 4.00

ABCDEFGH123 $ 5.55

I want the first two policy numbers to be shown as net off and the last two policy numbers should be separated.

[Vijay_Vittal_11]

Please help

[Vijay_Vittal_11]

Once a pair is found can this stop and not include the non-paired one for example -10, +10, +10. The formulas should leave the last +10