Removing Duplicate Values

[evinothk]

How to remove the duplicate records having positive and negative values netting with Zero value

Company Code	Account	Branch	Invoice	Currency	Reference	Value
XYXL	PL	SK	534	GBP	1GHA3451	56.00
XYXL	PL	SK	435	GBP	1GHA3451	(156.00)
ABCL	DF	MM	234	EUR	2ABHX567	100.00
GHLF	CF	ER	678	USD	5BHAX565	150.00
EDFT	IK	TN	756	EUR	5BHAX578	(555.00)
EDFT	IK	TN	891	EUR	5BHAX578	555.00
BHKG	MK	PT	678	GBP	4CA5X519	415.00
MGHT	YT	US	745	USD	1DT78901	234.00
GHJK	VG	HY	456	EUR	1FTGHJT7	(528.00)
GHJK	VG	HY	456	EUR	1FTGHJT7	528.00
PLHG	GH	KN	788	USD	3FATYH72	956.00
KLTY	BH	JN	102	GBP	9AFTYH64	1,000.00
HBHF	BC	JI	900	EUR	1UYCHJK3	166.20
BJIY	DC	JK	543	GBP	1HJU7501	755.00
LKHJ	ML	TR	876	DKK	1CMJ7602	845.00
PLOU	BH	NJ	991	SEK	1GHK4156	678.00
PLOU	BH	NJ	556	SEK	1GHK4156	(678.00)
LOMN	UJ	ED	518	USD	1LKK4758	200.00
VFSD	GH	SD	390	EUR	2MK4678	590.00

[danilang · Accepted solution]

Hello evinothk

To accomplish what you want, you have to summarize based on Reference and then join back to the original data.  Filter out the ones where the sum is 0 and you have your results

Dan

[evinothk]

Thanks a lot, it is great