Filtering OUToffseting positions

Question

not sure if this is even possible....

highlighted are the pairs of offsetting positions (the different colors indicate the offsetting pairs)

how do i know they're offsetting?

1) one has a"C" in column E, the other is BLANK

2) one is "B", the other is "S" in column D ("bought/sold" indicator)

3) they'll have OPPOSITE SIGNS (one positive, the other negative) in column A ("bought/sold AMT")

4) they'll have the same TAG code in column H

the other challenge is that these offesets won't always be next to each other; one could be in row 2 and the other could be in row 100.

Filtering Out Offsetting Pairs.xlsx

Gaurav_Dhama_ · Answer

Hi @daveyc3000 ,

In your data, add two new columns, one is absolute value and another is Credit Indicator.

What these two columns will contain > absolute column will contain all amounts as positive. Credit indicator will define if it was +ve or -ve.

Sort by on Tag and Abs amount. Now you can use Multi row formula to identify the combination as your pairs will be next to each other now. In case where Tag has multiple items with same amount, this can cause an exception. To tackle this add another grouping if possible.

daveyc3000 · Answer

Great to hear...pls see attached

Filtering Out Offsetting Pairs V2.xlsx

BRRLL99 · Answer

Please share sample of your input file

Offsetting can be done in alteryx