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 mocked up a workflow let me know what you think?

[mattypeggie]

This works perfectly for the example, unfortunately slight problems with the larger database. Will have a look and hopefully I can solve from here, many thanks!

[JosephSerpis · Accepted solution]

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

[mattypeggie]

Amazing, thank you very much. Helped me understand multi-row formulas :).