I have a table like this:
RecordID | Code | Amount |
1 | A | -500 |
2 | A | 500 |
3 | B | -500 |
4 | B | 300 |
5 | A | 500 |
The rule is that for a given Code if there are amounts that add up to 0, then I need to label the pairs for removal.
The tricky part is that there's no unique identifier and hence there's randomness. For example, since A on the first row is -500, A on the second row is 500, and A on the fifth row is also 500. Therefore, A on the first row adds either A on the second or the fifth row would result in 500. However, since there's only one -500, I would only need to remove one of the two 500s, not both. This means either row 2 or 5 -- both of which would be acceptable answers.
Please note that the answer I'm looking for should cover all scenarios, such as multiple -500 but one 500 and two -500 and two 500s (in which case all are marked for removal), etc. Only solutions that cover all possible scenarios would be marked accepted.
Outcome 1:
RecordID | Code | Amount | Label |
1 | A | -500 | Remove |
2 | A | 500 | Remove |
3 | B | -500 | Keep |
4 | B | 300 | Keep |
5 | A | 500 | Keep |
Outcome 2:
RecordID | Code | Amount | Label |
1 | A | -500 | Remove |
2 | A | 500 | Keep |
3 | B | -500 | Keep |
4 | B | 300 | Keep |
5 | A | 500 | Remove |
Any idea as to how I can achieve this?
Hi @45179902,
I believe a Multi-Row Formula tool should to the trick.
I copied your input and the below configuration seems to provide the required outcome.
Would this work for you?
Regards,
Remco