Remove offsetting entries with negative entries

[ahmad_mohamad]

Dear Alteryx community,

i would like to ask for your kind assistance/expertise in creating the following workflow,

I have a set of data (attached below as raw listing) in which there offsetting entrie. In excel i would normally solve the respective problem by adding 2 new columns and applying the respective 2 formulas below (please refer to attached excel, results) and filter the respective listing for duplicate entries.

Knock off identifier  =IF(B2="","",IF(B2<0,-B2&"_"&COUNTIF(B$2:B2,B2),B2&"_"&COUNTIF(B$2:B2,B2)))
Knock off identifier 2 =IF(COUNTIF(C:C,C2)=2,"x","")

To summarise the above, hope you could kindly design a workflow that can remove all offsetting entries from the raw listing to the desired result. 

i have attached a example of a raw listing and the desired result, i would really appreciate all the help :).

P.S i have explored the existing entries for offsetting entries but none of them are able to fully address/ solve my problem above the closest was the link below (but the solution doesn't take into consideration entries which lead to a negative

https://community.alteryx.com/t5/Alteryx-Server-Discussions/Remove-Reversing-Entries-in-a-Population...
balance).

[Ar13f]

Dear @ahmad_mohamad 

What do you mean by what I've written below?

[ahmad_mohamad]

Thank you for the suggested solution below,
however the respective formulas do not identify duplicates as they only add +1 to each ABS amount/

[terry10]

@ahmad_mohamad 

It isn't clear what you are asking. The excel formulas you show don't get your raw data to your desired result. If you provide a better sample data set, we'll be able to better help you.

[ahmad_mohamad]

Sorry for the confusion @terry10 ,

i have now edited the original post to further clarify the issue and to further directly reply to you i was hoping if the community would kindly assist in creating an Alteryx work flow that removes offsetting entries as the current available ones do not apply to my set of data (as my data has negative entries without offsetting entries).

Furthermore, the excel formulas provided above identify offsetting entries with an "x" mark in the "raw listing" in which i subsequently remove to obtain the desired result as shown in my screenshot below

So in excel i would take the following steps:

1. obtain raw data listing

2. add 2 new columns 
3. Insert knock off formul 1 and 2 into the 2 new columns

4 identified offsetting entries are marked as "x"

5.remove all entries marked with "x"

6. Clean listing is obtained

Hope this clarifies,

[terry10 · Accepted solution]

@ahmad_mohamad 

See attached workflow. Does this solve your problem?

Your first Excel formula is analogous to doing a grouped multi-row calculation in Alteryx; the second formula is analogous to doing a summarize calculation.

I hope this helps!

[Alessia11900]

Hi,

I was hoping you might be able to help me. I was looking for a workflow that would do the same as initially described above and stumbled across this thread. I have found the workflow you attached extremely helpful but I have run into one issue. When running this workflow there have been multiple times where the knock off count shows 3 or 4 (please see screenshot below highlighting where in the workflow this happens)

Am I correct in assuming I should not expect to see any knock off counts above 2?

Please note I am only seeing this issue on the data I am working on and not in the workflow seen above but am unable to share any screenshots of this workflow.

I would really appreciate any help on this!