Finding opposite values in a column from matching value in another column, given condition

mattypeggie

Example:

Client	Policy Ref	Trans Type	Sum Insured
ABCD Ltd	ABCD	Cancellation	£-500
ABCD Ltd	ABCD	Charge	£-30
ABCD Ltd	ABCD	Fee	£500
AFGH Ltd	AFGH	Endorsement	£290

I want to be able to select row 1 and 3 from this example, as well as other rows further down that have the same scenario.

The condition is that the trans type is Fee. I then want to search for all rows with the same Policy Ref as this. Then in each row, check if the Sum Insured is the opposite of the amount in the original Fee row.

I am very new to alteryx and having a real struggle with this, I can provide further examples if anyone needs.

Any help greatly appreciated. Thanks.

JosephSerpis

Hi @mattypeggie I came up with a second way that doesn't use a join may be more efficient with your large data.

Opposite_31072020.yxmd