Finding records that breaks net zero total

[byung0917]

Dear all. 

I need help!!

I need to find members in a group that make net zero. 

To simplify, here is an example. 

Every records are supposed to be netted out here. 

Fors example, Record B is paired with Record E to be zero in net. 

If a record is reversed, it should be always reversed with one record. 

For example, 100 will be netted out with -100. Never with two records of -50s. 

Group ID	Transaction ID	Amount
1	Record A	500
1	Record B	400
1	Record C	-200
1	Record D	200
1	Record E	-400
2	Record F	150
2	Record G	120
2	Record H	-150
2	Record I	-120
3	Record J	50
3	Record K	20
3	Record M	-20
3	Record N	30

So, from the sample data above, records below have to be carved out, so I can let people know these records have not been netted out. 

Expected output

Group ID	Transaction ID	Amount
1	Record A	500
3	Record N	30
3	Record J	50

I don't want to join with amount columns since there may be two records with same amounts. 

Do you experts have some good idea how to handle this?

Thank you so much Awesome Experts!

[DataNath · Accepted solution]

Hey @byung0917, how does this look? I added an extra 500 and -500 to test if the logic works with multiple of the same values, hence why the initial screenshot appears like I haven't gotten the desired outcome. Have attached the workflow for you to test yourself!

Let me know if not - happy to take another look. Interesting issue!

[grazitti_sapna · Accepted solution]

@byung0917, is not a great solution but it will work for your sample dataset.

Thanks!

[byung0917]

Thank you so much for sharing this!

[byung0917]

Thank you so much. It worked pretty well!!

[grazitti_sapna]

@byung0917, Happy to help 🙂