In this post, I will explain Excel absolute formula and duplicate finder in excel.
Let’s say, you are not having ERP in your Company and you need to find out which of your sales invoices are settled and what are the pending ones, you can extract these details from customer ledger very easily.
The old fashion way to do this task is to print the statement, then check each transaction one by one, eliminating the settled invoices, cancelling both items from debit and credit. But through excel, this is child’s play.
Follow the below steps:
Extract ledger in excel and open it. I will use one dummy example here to show the steps.
Data
| TYPE | DOC CODE | DATE | DESCRIPTION | DEBIT | CREDIT | BALANCE |
|---|---|---|---|---|---|---|
| JC-INV | 20010007 | 23/01/2020 | INVOICE AGAINST SALE OF GOODS | 29947 | 0 | 229196.81 |
| JC-INV | 20010008 | 23/01/2020 | INVOICE AGAINST SALE OF GOODS | 43927 | 0 | 273123.81 |
| JC-INV | 20010010 | 25/01/2020 | INVOICE AGAINST SALE OF GOODS | 5187 | 0 | 278310.81 |
| JC-INV | 20010019 | 31/01/2020 | INVOICE AGAINST SALE OF GOODS | 3441 | 0 | 273445.81 |
| JC-INV | 20010025 | 19/02/2020 | INVOICE AGAINST SALE OF GOODS | 8263 | 0 | 281708.81 |
| JC-INV | 20010030 | 29/02/2020 | INVOICE AGAINST SALE OF GOODS | 7349 | 0 | 289057.81 |
| JC-INV | 20010037 | 16/03/2020 | INVOICE AGAINST SALE OF GOODS | 14573 | 0 | 290546.81 |
| JC-INV | 20010050 | 25/03/2020 | INVOICE AGAINST SALE OF GOODS | 14262 | 0 | 296311.81 |
| JC-INV | 20010057 | 25/04/2020 | INVOICE AGAINST SALE OF GOODS | 11440 | 0 | 307751.81 |
| JC-INV | 20010058 | 25/04/2020 | INVOICE AGAINST SALE OF GOODS | 4212 | 0 | 311963.81 |
| JC-INV | 20010072 | 30/04/2020 | INVOICE AGAINST SALE OF GOODS | 3816 | 0 | 259291.81 |
| JC-INV | 20010075 | 16/05/2020 | INVOICE AGAINST SALE OF GOODS | 10000 | 0 | 196890.81 |
| JC-INV | 20010076 | 16/05/2020 | INVOICE AGAINST SALE OF GOODS | 1847 | 0 | 198737.81 |
| RV | RV200299 | 20/05/2020 | CHQ NO: 6046-(19-MAY-20) MCR: 9062 Settled for: 20010007 | 0 | 29947 | 168790.81 |
| JC-INV | 20010084 | 27/05/2020 | INVOICE AGAINST SALE OF GOODS | 5272 | 0 | 174062.81 |
| JC-INV | 20010088 | 28/05/2020 | INVOICE AGAINST SALE OF GOODS | 30694.9 | 0 | 204757.71 |
| JC-INV | 20010089 | 28/05/2020 | INVOICE AGAINST SALE OF GOODS | 7130 | 0 | 211887.71 |
| JC-INV | 20010091 | 30/05/2020 | INVOICE AGAINST SALE OF GOODS | 1944 | 0 | 213831.71 |
| RV | RV200311 | 01/06/2020 | CHQ NO: 6047-(29-MAY-20) MCR: 9063 Settled for: 20010008 | 0 | 43927 | 169904.71 |
| RV | RV200312 | 01/06/2020 | CHQ NO: 6060-(26-MAY-20) MCR: 9064 Settled for: 20010019 | 0 | 3441 | 166463.71 |
| RV | RV200325 | 07/06/2020 | CHQ NO: 6086-(07-JUN-20) MCR: 9070 Settled for: 20010010 | 0 | 5187 | 161276.71 |
| RV | RV200342 | 14/06/2020 | CHQ NO: 6150-(12-JUN-20) MCR: 9083 Settled for: 20010025 | 0 | 8263 | 153013.71 |
| JC-INV | 20010096 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 9263 | 0 | 162276.71 |
| JC-INV | 20010097 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 1944 | 0 | 164220.71 |
| JC-INV | 20010098 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 8293 | 0 | 172513.71 |
| JC-INV | 20010099 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 6175 | 0 | 178688.71 |
| JC-INV | 20010105 | 28/06/2020 | INVOICE AGAINST SALE OF GOODS | 9257 | 0 | 187945.71 |
| JC-INV | 20010106 | 28/06/2020 | INVOICE AGAINST SALE OF GOODS | 14370 | 0 | 202315.71 |
| JC-INV | 20010124 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 3328 | 0 | 205643.71 |
| JC-INV | 20010125 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 1936 | 0 | 207579.71 |
| JC-INV | 20010126 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 6921 | 0 | 214500.71 |
| JC-INV | 20010127 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 12783 | 0 | 227283.71 |
| JC-INV | 20010132 | 30/07/2020 | INVOICE AGAINST SALE OF GOODS | 14950 | 0 | 242233.71 |
| RV | RV200488 | 12/08/2020 | CHQ NO: 6261-(04-AUG-20) MCR: 9151 Settled for: 20010030 | 0 | 7349 | 234884.71 |
| RV | RV200502 | 19/08/2020 | CHQ NO: 6312-(19-AUG-20) MCR: 6312 Settled for: 20010037, 20010050 | 0 | 28835 | 206049.71 |
| JOURNAL | JV201331 | 25/08/2020 | ADVANCE RECOVERY FOR MIRRIKH JOB # 0119089 P.O # 3179 Settled for: 20010106 | 0 | 14370 | 191679.71 |
| JC-INV | 20010156 | 31/08/2020 | INVOICE AGAINST SALE OF GOODS | 4727 | 0 | 196406.71 |
| RV | RV200569 | 10/09/2020 | CHQ NO: 6376-(10-SEP-20) MCR: 9178 Settled for: 20010057 | 0 | 11440 | 184966.71 |
| JC-INV | 20010159 | 12/09/2020 | INVOICE AGAINST SALE OF GOODS | 5200 | 0 | 190166.71 |
| JC-INV | 20010160 | 12/09/2020 | INVOICE AGAINST SALE OF GOODS | 17614 | 0 | 207780.71 |
| RV | RV200576 | 13/09/2020 | CHQ NO: 6382-(11-SEP-20) MCR: 9179 Settled for: 20010058 | 0 | 4212 | 203568.71 |
| RV | RV200625 | 27/09/2020 | CHQ NO: 6446-(26-SEP-20) MCR: 9313 Settled for: 20010075, 20010072 | 0 | 13816 | 189752.71 |
| JC-INV | 20010173 | 30/09/2020 | INVOICE AGAINST SALE OF GOODS | 4968 | 0 | 194720.71 |
| JC-INV | 20010180 | 11/10/2020 | INVOICE AGAINST SALE OF GOODS | 10607 | 0 | 205327.71 |
| RV | RV200676 | 13/10/2020 | CHQ NO: 6521-(12-OCT-20) MCR: 9311 Settled for: 20010076, 20010091, 20010084 | 0 | 9063 | 196264.71 |
| RV | RV200677 | 13/10/2020 | CHQ NO: 6528-(12-OCT-20) MCR: 9312 Settled for: 20010089 | 0 | 7130 | 189134.71 |
| RV | RV200716 | 28/10/2020 | CHQ NO: 6550-(28-OCT-20) Settled for: 20010099 | 0 | 6175 | 182959.71 |
In a new column, subtract Credit from Debit. (Dr – Cr) and name it Net. This will bring both debit and credit into one column, converting all debit positive and credit negative. If your ledger already have Debit / credit in one column, then skip this step.
Net column
| TYPE | DOC CODE | DATE | DESCRIPTION | DEBIT | CREDIT | BALANCE | NET |
|---|---|---|---|---|---|---|---|
| JC-INV | 20010007 | 23/01/2020 | INVOICE AGAINST SALE OF GOODS | 29947 | 0 | 229196.81 | 29947 |
| JC-INV | 20010008 | 23/01/2020 | INVOICE AGAINST SALE OF GOODS | 43927 | 0 | 273123.81 | 43927 |
| JC-INV | 20010010 | 25/01/2020 | INVOICE AGAINST SALE OF GOODS | 5187 | 0 | 278310.81 | 5187 |
| JC-INV | 20010019 | 31/01/2020 | INVOICE AGAINST SALE OF GOODS | 3441 | 0 | 273445.81 | 3441 |
| JC-INV | 20010025 | 19/02/2020 | INVOICE AGAINST SALE OF GOODS | 8263 | 0 | 281708.81 | 8263 |
| JC-INV | 20010030 | 29/02/2020 | INVOICE AGAINST SALE OF GOODS | 7349 | 0 | 289057.81 | 7349 |
| JC-INV | 20010037 | 16/03/2020 | INVOICE AGAINST SALE OF GOODS | 14573 | 0 | 290546.81 | 14573 |
| JC-INV | 20010050 | 25/03/2020 | INVOICE AGAINST SALE OF GOODS | 14262 | 0 | 296311.81 | 14262 |
| JC-INV | 20010057 | 25/04/2020 | INVOICE AGAINST SALE OF GOODS | 11440 | 0 | 307751.81 | 11440 |
| JC-INV | 20010058 | 25/04/2020 | INVOICE AGAINST SALE OF GOODS | 4212 | 0 | 311963.81 | 4212 |
| JC-INV | 20010072 | 30/04/2020 | INVOICE AGAINST SALE OF GOODS | 3816 | 0 | 259291.81 | 3816 |
| JC-INV | 20010075 | 16/05/2020 | INVOICE AGAINST SALE OF GOODS | 10000 | 0 | 196890.81 | 10000 |
| JC-INV | 20010076 | 16/05/2020 | INVOICE AGAINST SALE OF GOODS | 1847 | 0 | 198737.81 | 1847 |
| RV | RV200299 | 20/05/2020 | CHQ NO: 6046-(19-MAY-20) MCR: 9062 Settled for: 20010007 | 0 | 29947 | 168790.81 | -29947 |
| JC-INV | 20010084 | 27/05/2020 | INVOICE AGAINST SALE OF GOODS | 5272 | 0 | 174062.81 | 5272 |
| JC-INV | 20010088 | 28/05/2020 | INVOICE AGAINST SALE OF GOODS | 30694.9 | 0 | 204757.71 | 30694.9 |
| JC-INV | 20010089 | 28/05/2020 | INVOICE AGAINST SALE OF GOODS | 7130 | 0 | 211887.71 | 7130 |
| JC-INV | 20010091 | 30/05/2020 | INVOICE AGAINST SALE OF GOODS | 1944 | 0 | 213831.71 | 1944 |
| RV | RV200311 | 01/06/2020 | CHQ NO: 6047-(29-MAY-20) MCR: 9063 Settled for: 20010008 | 0 | 43927 | 169904.71 | -43927 |
| RV | RV200312 | 01/06/2020 | CHQ NO: 6060-(26-MAY-20) MCR: 9064 Settled for: 20010019 | 0 | 3441 | 166463.71 | -3441 |
| RV | RV200325 | 07/06/2020 | CHQ NO: 6086-(07-JUN-20) MCR: 9070 Settled for: 20010010 | 0 | 5187 | 161276.71 | -5187 |
| RV | RV200342 | 14/06/2020 | CHQ NO: 6150-(12-JUN-20) MCR: 9083 Settled for: 20010025 | 0 | 8263 | 153013.71 | -8263 |
| JC-INV | 20010096 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 9263 | 0 | 162276.71 | 9263 |
| JC-INV | 20010097 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 1944 | 0 | 164220.71 | 1944 |
| JC-INV | 20010098 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 8293 | 0 | 172513.71 | 8293 |
| JC-INV | 20010099 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 6175 | 0 | 178688.71 | 6175 |
| JC-INV | 20010105 | 28/06/2020 | INVOICE AGAINST SALE OF GOODS | 9257 | 0 | 187945.71 | 9257 |
| JC-INV | 20010106 | 28/06/2020 | INVOICE AGAINST SALE OF GOODS | 14370 | 0 | 202315.71 | 14370 |
| JC-INV | 20010124 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 3328 | 0 | 205643.71 | 3328 |
| JC-INV | 20010125 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 1936 | 0 | 207579.71 | 1936 |
| JC-INV | 20010126 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 6921 | 0 | 214500.71 | 6921 |
| JC-INV | 20010127 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 12783 | 0 | 227283.71 | 12783 |
| JC-INV | 20010132 | 30/07/2020 | INVOICE AGAINST SALE OF GOODS | 14950 | 0 | 242233.71 | 14950 |
| RV | RV200488 | 12/08/2020 | CHQ NO: 6261-(04-AUG-20) MCR: 9151 Settled for: 20010030 | 0 | 7349 | 234884.71 | -7349 |
| RV | RV200502 | 19/08/2020 | CHQ NO: 6312-(19-AUG-20) MCR: 6312 Settled for: 20010037, 20010050 | 0 | 28835 | 206049.71 | -28835 |
| JOURNAL | JV201331 | 25/08/2020 | ADVANCE RECOVERY FOR MIRRIKH JOB # 0119089 P.O # 3179 Settled for: 20010106 | 0 | 14370 | 191679.71 | -14370 |
| JC-INV | 20010156 | 31/08/2020 | INVOICE AGAINST SALE OF GOODS | 4727 | 0 | 196406.71 | 4727 |
| RV | RV200569 | 10/09/2020 | CHQ NO: 6376-(10-SEP-20) MCR: 9178 Settled for: 20010057 | 0 | 11440 | 184966.71 | -11440 |
| JC-INV | 20010159 | 12/09/2020 | INVOICE AGAINST SALE OF GOODS | 5200 | 0 | 190166.71 | 5200 |
| JC-INV | 20010160 | 12/09/2020 | INVOICE AGAINST SALE OF GOODS | 17614 | 0 | 207780.71 | 17614 |
| RV | RV200576 | 13/09/2020 | CHQ NO: 6382-(11-SEP-20) MCR: 9179 Settled for: 20010058 | 0 | 4212 | 203568.71 | -4212 |
| RV | RV200625 | 27/09/2020 | CHQ NO: 6446-(26-SEP-20) MCR: 9313 Settled for: 20010075, 20010072 | 0 | 13816 | 189752.71 | -13816 |
| JC-INV | 20010173 | 30/09/2020 | INVOICE AGAINST SALE OF GOODS | 4968 | 0 | 194720.71 | 4968 |
| JC-INV | 20010180 | 11/10/2020 | INVOICE AGAINST SALE OF GOODS | 10607 | 0 | 205327.71 | 10607 |
| RV | RV200676 | 13/10/2020 | CHQ NO: 6521-(12-OCT-20) MCR: 9311 Settled for: 20010076, 20010091, 20010084 | 0 | 9063 | 196264.71 | -9063 |
| RV | RV200677 | 13/10/2020 | CHQ NO: 6528-(12-OCT-20) MCR: 9312 Settled for: 20010089 | 0 | 7130 | 189134.71 | -7130 |
| RV | RV200716 | 28/10/2020 | CHQ NO: 6550-(28-OCT-20) Settled for: 20010099 | 0 | 6175 | 182959.71 | -6175 |
Now use absolute formula. (ABS). Use = sign and then type ABS(, after that select cell from Net column, close bracket, and press enter. Apply it to net column. This will make debit credit figures absolute.
Absolute formula
| TYPE | DOC CODE | DATE | DESCRIPTION | DEBIT | CREDIT | BALANCE | NET | ABS |
|---|---|---|---|---|---|---|---|---|
| JC-INV | 20010007 | 23/01/2020 | INVOICE AGAINST SALE OF GOODS | 29947 | 0 | 229196.81 | 29947 | 29947 |
| JC-INV | 20010008 | 23/01/2020 | INVOICE AGAINST SALE OF GOODS | 43927 | 0 | 273123.81 | 43927 | 43927 |
| JC-INV | 20010010 | 25/01/2020 | INVOICE AGAINST SALE OF GOODS | 5187 | 0 | 278310.81 | 5187 | 5187 |
| JC-INV | 20010019 | 31/01/2020 | INVOICE AGAINST SALE OF GOODS | 3441 | 0 | 273445.81 | 3441 | 3441 |
| JC-INV | 20010025 | 19/02/2020 | INVOICE AGAINST SALE OF GOODS | 8263 | 0 | 281708.81 | 8263 | 8263 |
| JC-INV | 20010030 | 29/02/2020 | INVOICE AGAINST SALE OF GOODS | 7349 | 0 | 289057.81 | 7349 | 7349 |
| JC-INV | 20010037 | 16/03/2020 | INVOICE AGAINST SALE OF GOODS | 14573 | 0 | 290546.81 | 14573 | 14573 |
| JC-INV | 20010050 | 25/03/2020 | INVOICE AGAINST SALE OF GOODS | 14262 | 0 | 296311.81 | 14262 | 14262 |
| JC-INV | 20010057 | 25/04/2020 | INVOICE AGAINST SALE OF GOODS | 11440 | 0 | 307751.81 | 11440 | 11440 |
| JC-INV | 20010058 | 25/04/2020 | INVOICE AGAINST SALE OF GOODS | 4212 | 0 | 311963.81 | 4212 | 4212 |
| JC-INV | 20010072 | 30/04/2020 | INVOICE AGAINST SALE OF GOODS | 3816 | 0 | 259291.81 | 3816 | 3816 |
| JC-INV | 20010075 | 16/05/2020 | INVOICE AGAINST SALE OF GOODS | 10000 | 0 | 196890.81 | 10000 | 10000 |
| JC-INV | 20010076 | 16/05/2020 | INVOICE AGAINST SALE OF GOODS | 1847 | 0 | 198737.81 | 1847 | 1847 |
| RV | RV200299 | 20/05/2020 | CHQ NO: 6046-(19-MAY-20) MCR: 9062 Settled for: 20010007 | 0 | 29947 | 168790.81 | -29947 | 29947 |
| JC-INV | 20010084 | 27/05/2020 | INVOICE AGAINST SALE OF GOODS | 5272 | 0 | 174062.81 | 5272 | 5272 |
| JC-INV | 20010088 | 28/05/2020 | INVOICE AGAINST SALE OF GOODS | 30694.9 | 0 | 204757.71 | 30694.9 | 30694.9 |
| JC-INV | 20010089 | 28/05/2020 | INVOICE AGAINST SALE OF GOODS | 7130 | 0 | 211887.71 | 7130 | 7130 |
| JC-INV | 20010091 | 30/05/2020 | INVOICE AGAINST SALE OF GOODS | 1944 | 0 | 213831.71 | 1944 | 1944 |
| RV | RV200311 | 01/06/2020 | CHQ NO: 6047-(29-MAY-20) MCR: 9063 Settled for: 20010008 | 0 | 43927 | 169904.71 | -43927 | 43927 |
| RV | RV200312 | 01/06/2020 | CHQ NO: 6060-(26-MAY-20) MCR: 9064 Settled for: 20010019 | 0 | 3441 | 166463.71 | -3441 | 3441 |
| RV | RV200325 | 07/06/2020 | CHQ NO: 6086-(07-JUN-20) MCR: 9070 Settled for: 20010010 | 0 | 5187 | 161276.71 | -5187 | 5187 |
| RV | RV200342 | 14/06/2020 | CHQ NO: 6150-(12-JUN-20) MCR: 9083 Settled for: 20010025 | 0 | 8263 | 153013.71 | -8263 | 8263 |
| JC-INV | 20010096 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 9263 | 0 | 162276.71 | 9263 | 9263 |
| JC-INV | 20010097 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 1944 | 0 | 164220.71 | 1944 | 1944 |
| JC-INV | 20010098 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 8293 | 0 | 172513.71 | 8293 | 8293 |
| JC-INV | 20010099 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 6175 | 0 | 178688.71 | 6175 | 6175 |
| JC-INV | 20010105 | 28/06/2020 | INVOICE AGAINST SALE OF GOODS | 9257 | 0 | 187945.71 | 9257 | 9257 |
| JC-INV | 20010106 | 28/06/2020 | INVOICE AGAINST SALE OF GOODS | 14370 | 0 | 202315.71 | 14370 | 14370 |
| JC-INV | 20010124 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 3328 | 0 | 205643.71 | 3328 | 3328 |
| JC-INV | 20010125 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 1936 | 0 | 207579.71 | 1936 | 1936 |
| JC-INV | 20010126 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 6921 | 0 | 214500.71 | 6921 | 6921 |
| JC-INV | 20010127 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 12783 | 0 | 227283.71 | 12783 | 12783 |
| JC-INV | 20010132 | 30/07/2020 | INVOICE AGAINST SALE OF GOODS | 14950 | 0 | 242233.71 | 14950 | 14950 |
| RV | RV200488 | 12/08/2020 | CHQ NO: 6261-(04-AUG-20) MCR: 9151 Settled for: 20010030 | 0 | 7349 | 234884.71 | -7349 | 7349 |
| RV | RV200502 | 19/08/2020 | CHQ NO: 6312-(19-AUG-20) MCR: 6312 Settled for: 20010037, 20010050 | 0 | 28835 | 206049.71 | -28835 | 28835 |
| JOURNAL | JV201331 | 25/08/2020 | ADVANCE RECOVERY FOR MIRRIKH JOB # 0119089 P.O # 3179 Settled for: 20010106 | 0 | 14370 | 191679.71 | -14370 | 14370 |
| JC-INV | 20010156 | 31/08/2020 | INVOICE AGAINST SALE OF GOODS | 4727 | 0 | 196406.71 | 4727 | 4727 |
| RV | RV200569 | 10/09/2020 | CHQ NO: 6376-(10-SEP-20) MCR: 9178 Settled for: 20010057 | 0 | 11440 | 184966.71 | -11440 | 11440 |
| JC-INV | 20010159 | 12/09/2020 | INVOICE AGAINST SALE OF GOODS | 5200 | 0 | 190166.71 | 5200 | 5200 |
| JC-INV | 20010160 | 12/09/2020 | INVOICE AGAINST SALE OF GOODS | 17614 | 0 | 207780.71 | 17614 | 17614 |
| RV | RV200576 | 13/09/2020 | CHQ NO: 6382-(11-SEP-20) MCR: 9179 Settled for: 20010058 | 0 | 4212 | 203568.71 | -4212 | 4212 |
| RV | RV200625 | 27/09/2020 | CHQ NO: 6446-(26-SEP-20) MCR: 9313 Settled for: 20010075, 20010072 | 0 | 13816 | 189752.71 | -13816 | 13816 |
| JC-INV | 20010173 | 30/09/2020 | INVOICE AGAINST SALE OF GOODS | 4968 | 0 | 194720.71 | 4968 | 4968 |
| JC-INV | 20010180 | 11/10/2020 | INVOICE AGAINST SALE OF GOODS | 10607 | 0 | 205327.71 | 10607 | 10607 |
| RV | RV200676 | 13/10/2020 | CHQ NO: 6521-(12-OCT-20) MCR: 9311 Settled for: 20010076, 20010091, 20010084 | 0 | 9063 | 196264.71 | -9063 | 9063 |
| RV | RV200677 | 13/10/2020 | CHQ NO: 6528-(12-OCT-20) MCR: 9312 Settled for: 20010089 | 0 | 7130 | 189134.71 | -7130 | 7130 |
| RV | RV200716 | 28/10/2020 | CHQ NO: 6550-(28-OCT-20) Settled for: 20010099 | 0 | 6175 | 182959.71 | -6175 | 6175 |
After that, sort ABS column either smallest to largest or largest to smallest.
You can find sort option in Home tab and Data Tab.
Now we will use a tool to find duplicates. (Same amount invoiced and settled).
Duplicate option, you can find by navigating Home > Conditional Formatting > Highlight Cell rules > Duplicate Values
Duplicate finder
| TYPE | DOC CODE | DATE | DESCRIPTION | DEBIT | CREDIT | BALANCE | NET | ABS |
|---|---|---|---|---|---|---|---|---|
| JC-INV | 20010008 | 23/01/2020 | INVOICE AGAINST SALE OF GOODS | 43927 | 0 | 273123.81 | 43927 | 43927 |
| RV | RV200311 | 01/06/2020 | CHQ NO: 6047-(29-MAY-20) MCR: 9063 Settled for: 20010008 | 0 | 43927 | 169904.71 | -43927 | 43927 |
| JC-INV | 20010088 | 28/05/2020 | INVOICE AGAINST SALE OF GOODS | 30694.9 | 0 | 204757.71 | 30694.9 | 30694.9 |
| JC-INV | 20010007 | 23/01/2020 | INVOICE AGAINST SALE OF GOODS | 29947 | 0 | 229196.81 | 29947 | 29947 |
| RV | RV200299 | 20/05/2020 | CHQ NO: 6046-(19-MAY-20) MCR: 9062 Settled for: 20010007 | 0 | 29947 | 168790.81 | -29947 | 29947 |
| RV | RV200502 | 19/08/2020 | CHQ NO: 6312-(19-AUG-20) MCR: 6312 Settled for: 20010037, 20010050 | 0 | 28835 | 206049.71 | -28835 | 28835 |
| JC-INV | 20010160 | 12/09/2020 | INVOICE AGAINST SALE OF GOODS | 17614 | 0 | 207780.71 | 17614 | 17614 |
| JC-INV | 20010132 | 30/07/2020 | INVOICE AGAINST SALE OF GOODS | 14950 | 0 | 242233.71 | 14950 | 14950 |
| JC-INV | 20010037 | 16/03/2020 | INVOICE AGAINST SALE OF GOODS | 14573 | 0 | 290546.81 | 14573 | 14573 |
| JC-INV | 20010106 | 28/06/2020 | INVOICE AGAINST SALE OF GOODS | 14370 | 0 | 202315.71 | 14370 | 14370 |
| JOURNAL | JV201331 | 25/08/2020 | ADVANCE RECOVERY FOR MIRRIKH JOB # 0119089 P.O # 3179 Settled for: 20010106 | 0 | 14370 | 191679.71 | -14370 | 14370 |
| JC-INV | 20010050 | 25/03/2020 | INVOICE AGAINST SALE OF GOODS | 14262 | 0 | 296311.81 | 14262 | 14262 |
| RV | RV200625 | 27/09/2020 | CHQ NO: 6446-(26-SEP-20) MCR: 9313 Settled for: 20010075, 20010072 | 0 | 13816 | 189752.71 | -13816 | 13816 |
| JC-INV | 20010127 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 12783 | 0 | 227283.71 | 12783 | 12783 |
| JC-INV | 20010057 | 25/04/2020 | INVOICE AGAINST SALE OF GOODS | 11440 | 0 | 307751.81 | 11440 | 11440 |
| RV | RV200569 | 10/09/2020 | CHQ NO: 6376-(10-SEP-20) MCR: 9178 Settled for: 20010057 | 0 | 11440 | 184966.71 | -11440 | 11440 |
| JC-INV | 20010180 | 11/10/2020 | INVOICE AGAINST SALE OF GOODS | 10607 | 0 | 205327.71 | 10607 | 10607 |
| JC-INV | 20010075 | 16/05/2020 | INVOICE AGAINST SALE OF GOODS | 10000 | 0 | 196890.81 | 10000 | 10000 |
| JC-INV | 20010096 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 9263 | 0 | 162276.71 | 9263 | 9263 |
| JC-INV | 20010105 | 28/06/2020 | INVOICE AGAINST SALE OF GOODS | 9257 | 0 | 187945.71 | 9257 | 9257 |
| RV | RV200676 | 13/10/2020 | CHQ NO: 6521-(12-OCT-20) MCR: 9311 Settled for: 20010076, 20010091, 20010084 | 0 | 9063 | 196264.71 | -9063 | 9063 |
| JC-INV | 20010098 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 8293 | 0 | 172513.71 | 8293 | 8293 |
| JC-INV | 20010025 | 19/02/2020 | INVOICE AGAINST SALE OF GOODS | 8263 | 0 | 281708.81 | 8263 | 8263 |
| RV | RV200342 | 14/06/2020 | CHQ NO: 6150-(12-JUN-20) MCR: 9083 Settled for: 20010025 | 0 | 8263 | 153013.71 | -8263 | 8263 |
| JC-INV | 20010030 | 29/02/2020 | INVOICE AGAINST SALE OF GOODS | 7349 | 0 | 289057.81 | 7349 | 7349 |
| RV | RV200488 | 12/08/2020 | CHQ NO: 6261-(04-AUG-20) MCR: 9151 Settled for: 20010030 | 0 | 7349 | 234884.71 | -7349 | 7349 |
| JC-INV | 20010089 | 28/05/2020 | INVOICE AGAINST SALE OF GOODS | 7130 | 0 | 211887.71 | 7130 | 7130 |
| RV | RV200677 | 13/10/2020 | CHQ NO: 6528-(12-OCT-20) MCR: 9312 Settled for: 20010089 | 0 | 7130 | 189134.71 | -7130 | 7130 |
| JC-INV | 20010126 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 6921 | 0 | 214500.71 | 6921 | 6921 |
| JC-INV | 20010099 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 6175 | 0 | 178688.71 | 6175 | 6175 |
| RV | RV200716 | 28/10/2020 | CHQ NO: 6550-(28-OCT-20) Settled for: 20010099 | 0 | 6175 | 182959.71 | -6175 | 6175 |
| JC-INV | 20010084 | 27/05/2020 | INVOICE AGAINST SALE OF GOODS | 5272 | 0 | 174062.81 | 5272 | 5272 |
| JC-INV | 20010159 | 12/09/2020 | INVOICE AGAINST SALE OF GOODS | 5200 | 0 | 190166.71 | 5200 | 5200 |
| JC-INV | 20010010 | 25/01/2020 | INVOICE AGAINST SALE OF GOODS | 5187 | 0 | 278310.81 | 5187 | 5187 |
| RV | RV200325 | 07/06/2020 | CHQ NO: 6086-(07-JUN-20) MCR: 9070 Settled for: 20010010 | 0 | 5187 | 161276.71 | -5187 | 5187 |
| JC-INV | 20010173 | 30/09/2020 | INVOICE AGAINST SALE OF GOODS | 4968 | 0 | 194720.71 | 4968 | 4968 |
| JC-INV | 20010156 | 31/08/2020 | INVOICE AGAINST SALE OF GOODS | 4727 | 0 | 196406.71 | 4727 | 4727 |
| JC-INV | 20010058 | 25/04/2020 | INVOICE AGAINST SALE OF GOODS | 4212 | 0 | 311963.81 | 4212 | 4212 |
| RV | RV200576 | 13/09/2020 | CHQ NO: 6382-(11-SEP-20) MCR: 9179 Settled for: 20010058 | 0 | 4212 | 203568.71 | -4212 | 4212 |
| JC-INV | 20010072 | 30/04/2020 | INVOICE AGAINST SALE OF GOODS | 3816 | 0 | 259291.81 | 3816 | 3816 |
| JC-INV | 20010019 | 31/01/2020 | INVOICE AGAINST SALE OF GOODS | 3441 | 0 | 273445.81 | 3441 | 3441 |
| RV | RV200312 | 01/06/2020 | CHQ NO: 6060-(26-MAY-20) MCR: 9064 Settled for: 20010019 | 0 | 3441 | 166463.71 | -3441 | 3441 |
| JC-INV | 20010124 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 3328 | 0 | 205643.71 | 3328 | 3328 |
| JC-INV | 20010091 | 30/05/2020 | INVOICE AGAINST SALE OF GOODS | 1944 | 0 | 213831.71 | 1944 | 1944 |
| JC-INV | 20010097 | 21/06/2020 | INVOICE AGAINST SALE OF GOODS | 1944 | 0 | 164220.71 | 1944 | 1944 |
| JC-INV | 20010125 | 27/07/2020 | INVOICE AGAINST SALE OF GOODS | 1936 | 0 | 207579.71 | 1936 | 1936 |
| JC-INV | 20010076 | 16/05/2020 | INVOICE AGAINST SALE OF GOODS | 1847 | 0 | 198737.81 | 1847 | 1847 |
Once you have got duplicate cells highlighted. Quickly review it and delete the invoices and receipts.
Now whatever left might be a payment received for more than one invoice or the invoices which are still unsettled.
Bonus tip: I like to disable gridlines in my workbook. It makes contents more visible. You can find the checkbox to disable it in the View Tab.
Absolute (ABS) formula and duplicate finder is useful for many other purposes. If you are using it already, let me know in the comments, how you make your life easy with Excel.
Back to home: usamawrites.xyz