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!
Solved! Go to 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!
Thank you so much for sharing this!
Thank you so much. It worked pretty well!!
@byung0917, Happy to help 🙂