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 CodeNameDescriptionBase AmountTransaction Reference6417210600Other Staff BenefitsEAP 11 10 2018 to 10 04-9,190.770001127528ARV6417210600Other Staff BenefitsEAP 11 10 2018 to 10 049,190.770001127528A6417210600Other Staff BenefitsEAP 11 10 2018 to 10 049,196.190001127528AA6436116010GST on claimable expensesEAP 11 10 2018 to 10 04-451.920001127528ARV6436116010GST on claimable expensesEAP 11 10 2018 to 10 04451.920001127528A6436116010GST on claimable expensesEAP 11 10 2018 to 10 04624.620001127528AA6321220100SRE-IT Software MaintenanceAug19:Accrual425,223.92ACC0819-TEC6321220100SRE-IT Software MaintenanceAug19:Accrual-425,223.92RevACC0819-TEC6321220100SRE-IT Software MaintenanceJul19:Accrual61,750.00ACC0719-FIN6321220100SRE-IT Software MaintenanceJul19:Accrual55,207.24ACC0719-TEC6321220100SRE-IT Software MaintenanceJul19:Accrual-61,750.00RevACC0719-FIN6321220100SRE-IT Software MaintenanceJul19:Accrual-55,207.24RevACC0719-TEC6321220100SRE-IT Software MaintenanceJun19:Accrual74,017.24ACC0619-TEC6321220100SRE-IT Software MaintenanceJun19:Accrual-74,017.24RevACC0619-TEC6321220100SRE-IT Software MaintenanceJun19:Accrual74,017.24RevACC0619-TEC6321220100SRE-IT Software MaintenanceJun19:Accrual-74,017.24RevACC0619-TEC6321120000Software AmortizationDEPRECIATION237.77DPRCN6321120000Software AmortizationDEPRECIATION237.77DPRCN6321120000Software AmortizationDEPRECIATION237.77DPRCN6321120000Software AmortizationDEPRECIATION237.78DPRCN6321120000Software AmortizationDEPRECIATION237.78DPRCN6409101000DonationDonation1,200.00INV001016409101000DonationDonation-1,200.00REVINV001016409101000DonationDonation1,200.00INV001016409101000DonationDonation-1,200.00REVINV00101 Future Account CodeNameDescriptionBase AmountTransaction ReferenceOffset6417210600Other Staff BenefitsEAP 11 10 2018 to 10 04-9,190.770001127528ARVOffset6417210600Other Staff BenefitsEAP 11 10 2018 to 10 049,190.770001127528AOffset6417210600Other Staff BenefitsEAP 11 10 2018 to 10 049,196.190001127528AANo offset6436116010GST on claimable expensesEAP 11 10 2018 to 10 04-451.920001127528ARVOffset6436116010GST on claimable expensesEAP 11 10 2018 to 10 04451.920001127528AOffset6436116010GST on claimable expensesEAP 11 10 2018 to 10 04624.620001127528AANo offset6321220100SRE-IT Software MaintenanceAug19:Accrual425,223.92ACC0819-TECOffset6321220100SRE-IT Software MaintenanceAug19:Accrual-425,223.92RevACC0819-TECOffset6321220100SRE-IT Software MaintenanceJul19:Accrual61,750.00ACC0719-FINOffset6321220100SRE-IT Software MaintenanceJul19:Accrual55,207.24ACC0719-TECOffset6321220100SRE-IT Software MaintenanceJul19:Accrual-61,750.00RevACC0719-FINOffset6321220100SRE-IT Software MaintenanceJul19:Accrual-55,207.24RevACC0719-TECOffset6321220100SRE-IT Software MaintenanceJun19:Accrual74,017.24ACC0619-TECOffset6321220100SRE-IT Software MaintenanceJun19:Accrual-74,017.24RevACC0619-TECOffset6321220100SRE-IT Software MaintenanceJun19:Accrual74,017.24RevACC0619-TECOffset6321220100SRE-IT Software MaintenanceJun19:Accrual-74,017.24RevACC0619-TECOffset6321120000Software AmortizationDEPRECIATION237.77DPRCNNo offset6321120000Software AmortizationDEPRECIATION237.77DPRCNNo offset6321120000Software AmortizationDEPRECIATION237.77DPRCNNo offset6321120000Software AmortizationDEPRECIATION237.78DPRCNNo offset6321120000Software AmortizationDEPRECIATION237.78DPRCNNo offset6409101000DonationDonation1,200.00INV00101Offset6409101000DonationDonation-1,200.00REVINV00101Offset6409101000DonationDonation1,200.00INV00101Offset6409101000DonationDonation-1,200.00REVINV00101Offset

Identify Duplicate Values for offseting (26082020updated)

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

Preparation

Accepted answers

vizAlter

Hi @Haokun — Try the attached solution.

(FYI, as per your data, "Base Amount" for (+)/(-) 74017.24 found two times, hence I simply repeated the main block of the workflow (as you did want to go with Macro). Check the workflow to know more.)