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

[vizAlter · Accepted solution]

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.)

[Haokun]

@grazitti_sapna @vizAlter 

hi, you might want to look at the data again if you can help me. I add one more scenario into the data set. Sorry about it..

[Haokun]

@vizAlter 

Hi, surprisingly your method works!!!! even with so many transaction in my production data.

I need to learn your methodology.

[Haokun]

@vizAlter 

Hi, I just checked my data and I found that there will be instance that there are more than 2 sets of offsetting. Do I need to repeat workflow 4 times to identify the offsetting transactions? For example,

Account Code	Account Name	Amount
6000000000	Marketing	100
6000000000	Marketing	100
6000000000	Marketing	100
6000000000	Marketing	100
6000000000	Marketing	-100
6000000000	Marketing	-100
6000000000	Marketing	-100
6000000000	Marketing	-100

[Haokun]

@vizAlter 

I think for the subsequent re-check we can use Macro, do you know how to do that?

[vizAlter]

Hi @Haokun —  Thank you! Happy to know that!

Yes, add (and extend) like I have shown in the given workflow, or

Run the workflow multiple times (only on the target records, not all rows every time) until you get the final outcome.

Building a Macro would be a bit more tricky on this.