offset based on Order number

[Amy123]

Hi Team,

Can anyone help me on below scenario:-

I have same order number for two amount say for example 

Order Number 121 has two transaction $ 100 Debit  and $ 100 Credit. Now, Without using the summarize tool how can we offset this two amount.

[cmcclellan]

That depends on the format of your data

[Amy123]

@cmcclellan 

Didn't get you 

[Amy123]

@cmcclellan 

I have multiple transactions from salary and I have to figure out the pair which is sum is not equal to zero

salary_Id	Transaction
123	7000
123	2000
123	7200
123	7000
123	-7000
123	7000
123	-7000
123	8000
456	3800
456	5000
789	-2500
789	-2500

[AZuc]

@Amy123 

I didn't get the whole idea but see if this workflow helps you (at least to identify who's paired and who's not)

[Amy123]

@AZuc 

No..this is not i am looking.

usually on Salary ID level some amount could be net off that i highlighted in Pivot column..

salary_Id	Transaction	Pivot
123	7000	7000
123	2000	2000
123	7200	7200
123	7000	0
123	-7000	0
123	7000	0
123	-7000	0
123	8000	8000
456	3800	3800
456	5000	5000
789	-2500	-2500
789	-2500	-2500

[AZuc]

@Amy123 see if this one works for you

[Amy123]

@AZuc 

Can you test this data..

Salary_Id	Transaction	Pivot
4859520	-60000	0
4859520	60000	0
4859520	108500	108500
4859520	-134700	134700
4871463	60000	60000
4871463	60000	60000
4944437	4918	4918
4944465	247395	247395
4973537	8198	8198
4985550	41500	41500
5019528	121.33	121.33
5019528	4853.15	4853.15
5019528	12479.54	12479.54
5019528	-17454.04	17454.04
5024390	-1688	0
5024390	1688	0
5032560	1250	0
5032560	-1250	0
5032560	-3572.8	3572.8
5032562	103250	0
5032562	-103250	0
5036373	4700	4700
5036373	178284.94	178284.94
5036373	-182984.94	182984.94
5057319	-10369	0
5057319	10369	0
5059944	-189386	0
5059944	189386	0
5077285	-475.05	0
5077285	475.05	0

[PangHC]

try this. 

1. ABS  (for grouping)
2. ID for each + and - (for groping as well)
3. Summarise by salary ID, ABS, ID. sum by transaction.
4. Join back later 

but i not understand why still positive even it original is negative. (you can add a abs function later.)

thanks @AZuc, after i open your workflow, only realise i do too much 😂

after:

before: