Identify Duplicate Values for offseting (26082020updated)

[Haokun]

Hi Everyone,

May I know how to identify the duplicate records having positive and negative values netting with Zero value? please see the below transactions, what I know if only the account code and name will be the same for those offseting transactions. Hope can have some simple workflow (no Macro would be the best)

Current

Account Code	Name	Description	Base Amount	Transaction Reference
6417210600	Other Staff Benefits	EAP   11 10 2018 to 10 04	-9,190.77	0001127528ARV
6417210600	Other Staff Benefits	EAP   11 10 2018 to 10 04	9,190.77	0001127528A
6417210600	Other Staff Benefits	EAP   11 10 2018 to 10 04	9,196.19	0001127528AA
6436116010	GST on claimable expenses	EAP   11 10 2018 to 10 04	-451.92	0001127528ARV
6436116010	GST on claimable expenses	EAP   11 10 2018 to 10 04	451.92	0001127528A
6436116010	GST on claimable expenses	EAP   11 10 2018 to 10 04	624.62	0001127528AA
6321220100	SRE-IT Software Maintenance	Aug19:Accrual	425,223.92	ACC0819-TEC
6321220100	SRE-IT Software Maintenance	Aug19:Accrual	-425,223.92	RevACC0819-TEC
6321220100	SRE-IT Software Maintenance	Jul19:Accrual	61,750.00	ACC0719-FIN
6321220100	SRE-IT Software Maintenance	Jul19:Accrual	55,207.24	ACC0719-TEC
6321220100	SRE-IT Software Maintenance	Jul19:Accrual	-61,750.00	RevACC0719-FIN
6321220100	SRE-IT Software Maintenance	Jul19:Accrual	-55,207.24	RevACC0719-TEC
6321220100	SRE-IT Software Maintenance	Jun19:Accrual	74,017.24	ACC0619-TEC
6321220100	SRE-IT Software Maintenance	Jun19:Accrual	-74,017.24	RevACC0619-TEC
6321220100	SRE-IT Software Maintenance	Jun19:Accrual	74,017.24	RevACC0619-TEC
6321220100	SRE-IT Software Maintenance	Jun19:Accrual	-74,017.24	RevACC0619-TEC
6321120000	Software Amortization	DEPRECIATION	237.77	DPRCN
6321120000	Software Amortization	DEPRECIATION	237.77	DPRCN
6321120000	Software Amortization	DEPRECIATION	237.77	DPRCN
6321120000	Software Amortization	DEPRECIATION	237.78	DPRCN
6321120000	Software Amortization	DEPRECIATION	237.78	DPRCN
6409101000	Donation	Donation	1,200.00	INV00101
6409101000	Donation	Donation	-1,200.00	REVINV00101
6409101000	Donation	Donation	1,200.00	INV00101
6409101000	Donation	Donation	-1,200.00	REVINV00101

Future

Account Code	Name	Description	Base Amount	Transaction Reference	Offset
6417210600	Other Staff Benefits	EAP   11 10 2018 to 10 04	-9,190.77	0001127528ARV	Offset
6417210600	Other Staff Benefits	EAP   11 10 2018 to 10 04	9,190.77	0001127528A	Offset
6417210600	Other Staff Benefits	EAP   11 10 2018 to 10 04	9,196.19	0001127528AA	No offset
6436116010	GST on claimable expenses	EAP   11 10 2018 to 10 04	-451.92	0001127528ARV	Offset
6436116010	GST on claimable expenses	EAP   11 10 2018 to 10 04	451.92	0001127528A	Offset
6436116010	GST on claimable expenses	EAP   11 10 2018 to 10 04	624.62	0001127528AA	No offset
6321220100	SRE-IT Software Maintenance	Aug19:Accrual	425,223.92	ACC0819-TEC	Offset
6321220100	SRE-IT Software Maintenance	Aug19:Accrual	-425,223.92	RevACC0819-TEC	Offset
6321220100	SRE-IT Software Maintenance	Jul19:Accrual	61,750.00	ACC0719-FIN	Offset
6321220100	SRE-IT Software Maintenance	Jul19:Accrual	55,207.24	ACC0719-TEC	Offset
6321220100	SRE-IT Software Maintenance	Jul19:Accrual	-61,750.00	RevACC0719-FIN	Offset
6321220100	SRE-IT Software Maintenance	Jul19:Accrual	-55,207.24	RevACC0719-TEC	Offset
6321220100	SRE-IT Software Maintenance	Jun19:Accrual	74,017.24	ACC0619-TEC	Offset
6321220100	SRE-IT Software Maintenance	Jun19:Accrual	-74,017.24	RevACC0619-TEC	Offset
6321220100	SRE-IT Software Maintenance	Jun19:Accrual	74,017.24	RevACC0619-TEC	Offset
6321220100	SRE-IT Software Maintenance	Jun19:Accrual	-74,017.24	RevACC0619-TEC	Offset
6321120000	Software Amortization	DEPRECIATION	237.77	DPRCN	No offset
6321120000	Software Amortization	DEPRECIATION	237.77	DPRCN	No offset
6321120000	Software Amortization	DEPRECIATION	237.77	DPRCN	No offset
6321120000	Software Amortization	DEPRECIATION	237.78	DPRCN	No offset
6321120000	Software Amortization	DEPRECIATION	237.78	DPRCN	No offset
6409101000	Donation	Donation	1,200.00	INV00101	Offset
6409101000	Donation	Donation	-1,200.00	REVINV00101	Offset
6409101000	Donation	Donation	1,200.00	INV00101	Offset
6409101000	Donation	Donation	-1,200.00	REVINV00101	Offset

[ponraj]

Sample workflow. Hope this is helpful. 

[vizAlter]

Hi @Haokun — Hope, the attached solution will help you.

You need to use "Sort" and "Multi-Row Formula":

[grazitti_sapna]

Hi @Haokun , try this if it works for you.

[Haokun]

Sorry, there is another set of transaction I forgot to include, are you able to help me again based on the updated transactions?

[Haokun]

hi @grazitti_sapna @vizAlter @ponraj 

There is an update to the sample data, would you please take a look again? I am so sorry for the inconveniences.

[grazitti_sapna]

Hi @Haokun, try this.

[vizAlter]

Hi @Haokun —  In your updated table, what is the logic behind "Journal No."?

Do you want to identify unique records based on "Account Code" + "Base Amount", or are you referring to the entire row?

And, last 5 rows showing "No offset":

[Haokun]

@vizAlter 

you can't use this journal number unfortunately...there is no other identifier

[Haokun]

@grazitti_sapna 

thank you so much!!!

but unfortunately when I am checking for the total offset and no offset amount, the total offset amount is not zero. I am checking where goes wrong. I have 80k over transactions, I am not sure if there is any rule not yet build in to cause the variance.