Remove records (randomness)

[45179902]

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?

[RemcoV]

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

[Raj]

Find the sol attached