Filtering OUToffseting positions

[daveyc3000]

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.

[BRRLL99]

Please share your expected output

same tag code 

with column B and C should be matched?

[daveyc3000]

the attached file is my output ....these positions were cut/removed and pasted out of the original data worksheet and put into another sheet called "Offsetting"

yes, they'll have the same TAG....they won't necessarily have the same amount in column B ("Net Amt") and thus it is not a column I look at

but, they would, yes, have the same amount in column C (again, opposite sign, one negative, one short)

ofc, pls keep in mind the other criteria (i.e., in column E, one will have "C", other blank, column D, one "B", the other "S")

[BRRLL99]

Please share sample of your input file

Offsetting can be done in alteryx 

[daveyc3000]

Great to hear...pls see attached

[Gaurav_Dhama_]

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.