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 |
Solved! Go to Solution.
Hi @Haokun — Hope, the attached solution will help you.
You need to use "Sort" and "Multi-Row Formula":
Sorry, there is another set of transaction I forgot to include, are you able to help me again based on the updated transactions?
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.
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":
you can't use this journal number unfortunately...there is no other identifier
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.