Dulpicate Postive & Negative Values Needs to be removed in Excel sheet - Based On Unique Order & Rep

[chechi_navi]

Order Salesrep Email Allocation % Amount **
9071442 XX@MM.com 100 34,200 Don't
9071442 XX@MM.com 100 7,524 Don't
9071442 yy@MM.com 100 34,200 Delete
9071442 yy@MM.com -100 (34,200) Delete
9071442 yy@MM.com 100 7,524 Delete
9071442 yy@MM.com -100 (7,524) Delete


A Col is Unique with Order Number
B Col has Sale Rep Email ID's which can be different for 1 Order which is there in current case
C Col Allocation of Amount "100" Refers full allocation "-100" Refers reversal of that allocation to particular Rep
E Col Amount "100" Refers full allocation "-100" Refers reversal of that allocation to particular Rep
In E col I need result for Unique Order & where Rep email ID's are same & One Positive & One negative values netting out Zero should be removed

 

[Raviprmr]

Hi Chechi,

PFA

 

[PCosta87]

Hi,

Welcome to the forum

Maybe using SUMIFS?
See attached

PS: @Raviprmr, just to add that, although I think your solution may work, I also believe it needs to be tweaked... as it is, it will highlight (to remove) rows even if both are positive (or both negative)... net result of sum should be "0" if I understood correctly.

 

[chechi_navi]

Thanks @ PCosta87 it really worked for me.

 

[chechi_navi]

Hi Chechi,

PFA

Thanks for Your response, However if I am increasing data in list this is making all rows highlighted as pink

 

[chechi_navi]

Great, Thanks it worked very well in this case

 

[chechi_navi]

Hi,

Welcome to the forum

Maybe using SUMIFS?
See attached

PS: @Raviprmr, just to add that, although I think your solution may work, I also believe it needs to be tweaked... as it is, it will highlight (to remove) rows even if both are positive (or both negative)... net result of sum should be "0" if I understood correctly.

Great, Thanks it worked very well in this case

 

[PCosta87]

Don't mention it