Remove offsetting entries with negative entries

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/td-p/701180
balance).

Knock Off raw listing.xlsx

Desired result.xlsx

Workflow

Datasets

Accepted answers

terry10

@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!

offset_community.yxmd

All comments

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

@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!

offset_community.yxmd

Quick Links

This months top contributors

mceleavey 382

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262