Absolute formula and duplicate finder in Excel

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

TYPEDOC CODEDATEDESCRIPTIONDEBITCREDITBALANCE
JC-INV2001000723/01/2020INVOICE AGAINST SALE OF GOODS299470229196.81
JC-INV2001000823/01/2020INVOICE AGAINST SALE OF GOODS439270273123.81
JC-INV2001001025/01/2020INVOICE AGAINST SALE OF GOODS51870278310.81
JC-INV2001001931/01/2020INVOICE AGAINST SALE OF GOODS34410273445.81
JC-INV2001002519/02/2020INVOICE AGAINST SALE OF GOODS82630281708.81
JC-INV2001003029/02/2020INVOICE AGAINST SALE OF GOODS73490289057.81
JC-INV2001003716/03/2020INVOICE AGAINST SALE OF GOODS145730290546.81
JC-INV2001005025/03/2020INVOICE AGAINST SALE OF GOODS142620296311.81
JC-INV2001005725/04/2020INVOICE AGAINST SALE OF GOODS114400307751.81
JC-INV2001005825/04/2020INVOICE AGAINST SALE OF GOODS42120311963.81
JC-INV2001007230/04/2020INVOICE AGAINST SALE OF GOODS38160259291.81
JC-INV2001007516/05/2020INVOICE AGAINST SALE OF GOODS100000196890.81
JC-INV2001007616/05/2020INVOICE AGAINST SALE OF GOODS18470198737.81
RVRV20029920/05/2020CHQ NO: 6046-(19-MAY-20) MCR: 9062 Settled for: 20010007029947168790.81
JC-INV2001008427/05/2020INVOICE AGAINST SALE OF GOODS52720174062.81
JC-INV2001008828/05/2020INVOICE AGAINST SALE OF GOODS30694.90204757.71
JC-INV2001008928/05/2020INVOICE AGAINST SALE OF GOODS71300211887.71
JC-INV2001009130/05/2020INVOICE AGAINST SALE OF GOODS19440213831.71
RVRV20031101/06/2020CHQ NO: 6047-(29-MAY-20) MCR: 9063 Settled for: 20010008043927169904.71
RVRV20031201/06/2020CHQ NO: 6060-(26-MAY-20) MCR: 9064 Settled for: 2001001903441166463.71
RVRV20032507/06/2020CHQ NO: 6086-(07-JUN-20) MCR: 9070 Settled for: 2001001005187161276.71
RVRV20034214/06/2020CHQ NO: 6150-(12-JUN-20) MCR: 9083 Settled for: 2001002508263153013.71
JC-INV2001009621/06/2020INVOICE AGAINST SALE OF GOODS92630162276.71
JC-INV2001009721/06/2020INVOICE AGAINST SALE OF GOODS19440164220.71
JC-INV2001009821/06/2020INVOICE AGAINST SALE OF GOODS82930172513.71
JC-INV2001009921/06/2020INVOICE AGAINST SALE OF GOODS61750178688.71
JC-INV2001010528/06/2020INVOICE AGAINST SALE OF GOODS92570187945.71
JC-INV2001010628/06/2020INVOICE AGAINST SALE OF GOODS143700202315.71
JC-INV2001012427/07/2020INVOICE AGAINST SALE OF GOODS33280205643.71
JC-INV2001012527/07/2020INVOICE AGAINST SALE OF GOODS19360207579.71
JC-INV2001012627/07/2020INVOICE AGAINST SALE OF GOODS69210214500.71
JC-INV2001012727/07/2020INVOICE AGAINST SALE OF GOODS127830227283.71
JC-INV2001013230/07/2020INVOICE AGAINST SALE OF GOODS149500242233.71
RVRV20048812/08/2020CHQ NO: 6261-(04-AUG-20) MCR: 9151 Settled for: 2001003007349234884.71
RVRV20050219/08/2020CHQ NO: 6312-(19-AUG-20) MCR: 6312 Settled for: 20010037, 20010050028835206049.71
JOURNALJV20133125/08/2020ADVANCE RECOVERY FOR MIRRIKH JOB # 0119089 P.O # 3179 Settled for: 20010106014370191679.71
JC-INV2001015631/08/2020INVOICE AGAINST SALE OF GOODS47270196406.71
RVRV20056910/09/2020CHQ NO: 6376-(10-SEP-20) MCR: 9178 Settled for: 20010057011440184966.71
JC-INV2001015912/09/2020INVOICE AGAINST SALE OF GOODS52000190166.71
JC-INV2001016012/09/2020INVOICE AGAINST SALE OF GOODS176140207780.71
RVRV20057613/09/2020CHQ NO: 6382-(11-SEP-20) MCR: 9179 Settled for: 2001005804212203568.71
RVRV20062527/09/2020CHQ NO: 6446-(26-SEP-20) MCR: 9313 Settled for: 20010075, 20010072013816189752.71
JC-INV2001017330/09/2020INVOICE AGAINST SALE OF GOODS49680194720.71
JC-INV2001018011/10/2020INVOICE AGAINST SALE OF GOODS106070205327.71
RVRV20067613/10/2020CHQ NO: 6521-(12-OCT-20) MCR: 9311 Settled for: 20010076, 20010091, 2001008409063196264.71
RVRV20067713/10/2020CHQ NO: 6528-(12-OCT-20) MCR: 9312 Settled for: 2001008907130189134.71
RVRV20071628/10/2020CHQ NO: 6550-(28-OCT-20) Settled for: 2001009906175182959.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

TYPEDOC CODEDATEDESCRIPTIONDEBITCREDITBALANCENET
JC-INV2001000723/01/2020INVOICE AGAINST SALE OF GOODS299470229196.8129947
JC-INV2001000823/01/2020INVOICE AGAINST SALE OF GOODS439270273123.8143927
JC-INV2001001025/01/2020INVOICE AGAINST SALE OF GOODS51870278310.815187
JC-INV2001001931/01/2020INVOICE AGAINST SALE OF GOODS34410273445.813441
JC-INV2001002519/02/2020INVOICE AGAINST SALE OF GOODS82630281708.818263
JC-INV2001003029/02/2020INVOICE AGAINST SALE OF GOODS73490289057.817349
JC-INV2001003716/03/2020INVOICE AGAINST SALE OF GOODS145730290546.8114573
JC-INV2001005025/03/2020INVOICE AGAINST SALE OF GOODS142620296311.8114262
JC-INV2001005725/04/2020INVOICE AGAINST SALE OF GOODS114400307751.8111440
JC-INV2001005825/04/2020INVOICE AGAINST SALE OF GOODS42120311963.814212
JC-INV2001007230/04/2020INVOICE AGAINST SALE OF GOODS38160259291.813816
JC-INV2001007516/05/2020INVOICE AGAINST SALE OF GOODS100000196890.8110000
JC-INV2001007616/05/2020INVOICE AGAINST SALE OF GOODS18470198737.811847
RVRV20029920/05/2020CHQ NO: 6046-(19-MAY-20) MCR: 9062 Settled for: 20010007029947168790.81-29947
JC-INV2001008427/05/2020INVOICE AGAINST SALE OF GOODS52720174062.815272
JC-INV2001008828/05/2020INVOICE AGAINST SALE OF GOODS30694.90204757.7130694.9
JC-INV2001008928/05/2020INVOICE AGAINST SALE OF GOODS71300211887.717130
JC-INV2001009130/05/2020INVOICE AGAINST SALE OF GOODS19440213831.711944
RVRV20031101/06/2020CHQ NO: 6047-(29-MAY-20) MCR: 9063 Settled for: 20010008043927169904.71-43927
RVRV20031201/06/2020CHQ NO: 6060-(26-MAY-20) MCR: 9064 Settled for: 2001001903441166463.71-3441
RVRV20032507/06/2020CHQ NO: 6086-(07-JUN-20) MCR: 9070 Settled for: 2001001005187161276.71-5187
RVRV20034214/06/2020CHQ NO: 6150-(12-JUN-20) MCR: 9083 Settled for: 2001002508263153013.71-8263
JC-INV2001009621/06/2020INVOICE AGAINST SALE OF GOODS92630162276.719263
JC-INV2001009721/06/2020INVOICE AGAINST SALE OF GOODS19440164220.711944
JC-INV2001009821/06/2020INVOICE AGAINST SALE OF GOODS82930172513.718293
JC-INV2001009921/06/2020INVOICE AGAINST SALE OF GOODS61750178688.716175
JC-INV2001010528/06/2020INVOICE AGAINST SALE OF GOODS92570187945.719257
JC-INV2001010628/06/2020INVOICE AGAINST SALE OF GOODS143700202315.7114370
JC-INV2001012427/07/2020INVOICE AGAINST SALE OF GOODS33280205643.713328
JC-INV2001012527/07/2020INVOICE AGAINST SALE OF GOODS19360207579.711936
JC-INV2001012627/07/2020INVOICE AGAINST SALE OF GOODS69210214500.716921
JC-INV2001012727/07/2020INVOICE AGAINST SALE OF GOODS127830227283.7112783
JC-INV2001013230/07/2020INVOICE AGAINST SALE OF GOODS149500242233.7114950
RVRV20048812/08/2020CHQ NO: 6261-(04-AUG-20) MCR: 9151 Settled for: 2001003007349234884.71-7349
RVRV20050219/08/2020CHQ NO: 6312-(19-AUG-20) MCR: 6312 Settled for: 20010037, 20010050028835206049.71-28835
JOURNALJV20133125/08/2020ADVANCE RECOVERY FOR MIRRIKH JOB # 0119089 P.O # 3179 Settled for: 20010106014370191679.71-14370
JC-INV2001015631/08/2020INVOICE AGAINST SALE OF GOODS47270196406.714727
RVRV20056910/09/2020CHQ NO: 6376-(10-SEP-20) MCR: 9178 Settled for: 20010057011440184966.71-11440
JC-INV2001015912/09/2020INVOICE AGAINST SALE OF GOODS52000190166.715200
JC-INV2001016012/09/2020INVOICE AGAINST SALE OF GOODS176140207780.7117614
RVRV20057613/09/2020CHQ NO: 6382-(11-SEP-20) MCR: 9179 Settled for: 2001005804212203568.71-4212
RVRV20062527/09/2020CHQ NO: 6446-(26-SEP-20) MCR: 9313 Settled for: 20010075, 20010072013816189752.71-13816
JC-INV2001017330/09/2020INVOICE AGAINST SALE OF GOODS49680194720.714968
JC-INV2001018011/10/2020INVOICE AGAINST SALE OF GOODS106070205327.7110607
RVRV20067613/10/2020CHQ NO: 6521-(12-OCT-20) MCR: 9311 Settled for: 20010076, 20010091, 2001008409063196264.71-9063
RVRV20067713/10/2020CHQ NO: 6528-(12-OCT-20) MCR: 9312 Settled for: 2001008907130189134.71-7130
RVRV20071628/10/2020CHQ NO: 6550-(28-OCT-20) Settled for: 2001009906175182959.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

TYPEDOC CODEDATEDESCRIPTIONDEBITCREDITBALANCENETABS
JC-INV2001000723/01/2020INVOICE AGAINST SALE OF GOODS299470229196.812994729947
JC-INV2001000823/01/2020INVOICE AGAINST SALE OF GOODS439270273123.814392743927
JC-INV2001001025/01/2020INVOICE AGAINST SALE OF GOODS51870278310.8151875187
JC-INV2001001931/01/2020INVOICE AGAINST SALE OF GOODS34410273445.8134413441
JC-INV2001002519/02/2020INVOICE AGAINST SALE OF GOODS82630281708.8182638263
JC-INV2001003029/02/2020INVOICE AGAINST SALE OF GOODS73490289057.8173497349
JC-INV2001003716/03/2020INVOICE AGAINST SALE OF GOODS145730290546.811457314573
JC-INV2001005025/03/2020INVOICE AGAINST SALE OF GOODS142620296311.811426214262
JC-INV2001005725/04/2020INVOICE AGAINST SALE OF GOODS114400307751.811144011440
JC-INV2001005825/04/2020INVOICE AGAINST SALE OF GOODS42120311963.8142124212
JC-INV2001007230/04/2020INVOICE AGAINST SALE OF GOODS38160259291.8138163816
JC-INV2001007516/05/2020INVOICE AGAINST SALE OF GOODS100000196890.811000010000
JC-INV2001007616/05/2020INVOICE AGAINST SALE OF GOODS18470198737.8118471847
RVRV20029920/05/2020CHQ NO: 6046-(19-MAY-20) MCR: 9062 Settled for: 20010007029947168790.81-2994729947
JC-INV2001008427/05/2020INVOICE AGAINST SALE OF GOODS52720174062.8152725272
JC-INV2001008828/05/2020INVOICE AGAINST SALE OF GOODS30694.90204757.7130694.930694.9
JC-INV2001008928/05/2020INVOICE AGAINST SALE OF GOODS71300211887.7171307130
JC-INV2001009130/05/2020INVOICE AGAINST SALE OF GOODS19440213831.7119441944
RVRV20031101/06/2020CHQ NO: 6047-(29-MAY-20) MCR: 9063 Settled for: 20010008043927169904.71-4392743927
RVRV20031201/06/2020CHQ NO: 6060-(26-MAY-20) MCR: 9064 Settled for: 2001001903441166463.71-34413441
RVRV20032507/06/2020CHQ NO: 6086-(07-JUN-20) MCR: 9070 Settled for: 2001001005187161276.71-51875187
RVRV20034214/06/2020CHQ NO: 6150-(12-JUN-20) MCR: 9083 Settled for: 2001002508263153013.71-82638263
JC-INV2001009621/06/2020INVOICE AGAINST SALE OF GOODS92630162276.7192639263
JC-INV2001009721/06/2020INVOICE AGAINST SALE OF GOODS19440164220.7119441944
JC-INV2001009821/06/2020INVOICE AGAINST SALE OF GOODS82930172513.7182938293
JC-INV2001009921/06/2020INVOICE AGAINST SALE OF GOODS61750178688.7161756175
JC-INV2001010528/06/2020INVOICE AGAINST SALE OF GOODS92570187945.7192579257
JC-INV2001010628/06/2020INVOICE AGAINST SALE OF GOODS143700202315.711437014370
JC-INV2001012427/07/2020INVOICE AGAINST SALE OF GOODS33280205643.7133283328
JC-INV2001012527/07/2020INVOICE AGAINST SALE OF GOODS19360207579.7119361936
JC-INV2001012627/07/2020INVOICE AGAINST SALE OF GOODS69210214500.7169216921
JC-INV2001012727/07/2020INVOICE AGAINST SALE OF GOODS127830227283.711278312783
JC-INV2001013230/07/2020INVOICE AGAINST SALE OF GOODS149500242233.711495014950
RVRV20048812/08/2020CHQ NO: 6261-(04-AUG-20) MCR: 9151 Settled for: 2001003007349234884.71-73497349
RVRV20050219/08/2020CHQ NO: 6312-(19-AUG-20) MCR: 6312 Settled for: 20010037, 20010050028835206049.71-2883528835
JOURNALJV20133125/08/2020ADVANCE RECOVERY FOR MIRRIKH JOB # 0119089 P.O # 3179 Settled for: 20010106014370191679.71-1437014370
JC-INV2001015631/08/2020INVOICE AGAINST SALE OF GOODS47270196406.7147274727
RVRV20056910/09/2020CHQ NO: 6376-(10-SEP-20) MCR: 9178 Settled for: 20010057011440184966.71-1144011440
JC-INV2001015912/09/2020INVOICE AGAINST SALE OF GOODS52000190166.7152005200
JC-INV2001016012/09/2020INVOICE AGAINST SALE OF GOODS176140207780.711761417614
RVRV20057613/09/2020CHQ NO: 6382-(11-SEP-20) MCR: 9179 Settled for: 2001005804212203568.71-42124212
RVRV20062527/09/2020CHQ NO: 6446-(26-SEP-20) MCR: 9313 Settled for: 20010075, 20010072013816189752.71-1381613816
JC-INV2001017330/09/2020INVOICE AGAINST SALE OF GOODS49680194720.7149684968
JC-INV2001018011/10/2020INVOICE AGAINST SALE OF GOODS106070205327.711060710607
RVRV20067613/10/2020CHQ NO: 6521-(12-OCT-20) MCR: 9311 Settled for: 20010076, 20010091, 2001008409063196264.71-90639063
RVRV20067713/10/2020CHQ NO: 6528-(12-OCT-20) MCR: 9312 Settled for: 2001008907130189134.71-71307130
RVRV20071628/10/2020CHQ NO: 6550-(28-OCT-20) Settled for: 2001009906175182959.71-61756175

After that, sort ABS column either smallest to largest or largest to smallest.

You can find sort option in Home tab and Data Tab.

sort option in home tab
sort option in 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 values

Duplicate finder

TYPEDOC CODEDATEDESCRIPTIONDEBITCREDITBALANCENETABS
JC-INV2001000823/01/2020INVOICE AGAINST SALE OF GOODS439270273123.8143927

43927

RVRV20031101/06/2020CHQ NO: 6047-(29-MAY-20) MCR: 9063 Settled for: 20010008043927169904.71-43927

43927

JC-INV2001008828/05/2020INVOICE AGAINST SALE OF GOODS30694.90204757.7130694.930694.9
JC-INV2001000723/01/2020INVOICE AGAINST SALE OF GOODS299470229196.8129947

29947

RVRV20029920/05/2020CHQ NO: 6046-(19-MAY-20) MCR: 9062 Settled for: 20010007029947168790.81-29947

29947

RVRV20050219/08/2020CHQ NO: 6312-(19-AUG-20) MCR: 6312 Settled for: 20010037, 20010050028835206049.71-2883528835
JC-INV2001016012/09/2020INVOICE AGAINST SALE OF GOODS176140207780.711761417614
JC-INV2001013230/07/2020INVOICE AGAINST SALE OF GOODS149500242233.711495014950
JC-INV2001003716/03/2020INVOICE AGAINST SALE OF GOODS145730290546.811457314573
JC-INV2001010628/06/2020INVOICE AGAINST SALE OF GOODS143700202315.7114370

14370

JOURNALJV20133125/08/2020ADVANCE RECOVERY FOR MIRRIKH JOB # 0119089 P.O # 3179 Settled for: 20010106014370191679.71-14370

14370

JC-INV2001005025/03/2020INVOICE AGAINST SALE OF GOODS142620296311.811426214262
RVRV20062527/09/2020CHQ NO: 6446-(26-SEP-20) MCR: 9313 Settled for: 20010075, 20010072013816189752.71-1381613816
JC-INV2001012727/07/2020INVOICE AGAINST SALE OF GOODS127830227283.711278312783
JC-INV2001005725/04/2020INVOICE AGAINST SALE OF GOODS114400307751.8111440

11440

RVRV20056910/09/2020CHQ NO: 6376-(10-SEP-20) MCR: 9178 Settled for: 20010057011440184966.71-11440

11440

JC-INV2001018011/10/2020INVOICE AGAINST SALE OF GOODS106070205327.711060710607
JC-INV2001007516/05/2020INVOICE AGAINST SALE OF GOODS100000196890.811000010000
JC-INV2001009621/06/2020INVOICE AGAINST SALE OF GOODS92630162276.7192639263
JC-INV2001010528/06/2020INVOICE AGAINST SALE OF GOODS92570187945.7192579257
RVRV20067613/10/2020CHQ NO: 6521-(12-OCT-20) MCR: 9311 Settled for: 20010076, 20010091, 2001008409063196264.71-90639063
JC-INV2001009821/06/2020INVOICE AGAINST SALE OF GOODS82930172513.7182938293
JC-INV2001002519/02/2020INVOICE AGAINST SALE OF GOODS82630281708.818263

8263

RVRV20034214/06/2020CHQ NO: 6150-(12-JUN-20) MCR: 9083 Settled for: 2001002508263153013.71-8263

8263

JC-INV2001003029/02/2020INVOICE AGAINST SALE OF GOODS73490289057.817349

7349

RVRV20048812/08/2020CHQ NO: 6261-(04-AUG-20) MCR: 9151 Settled for: 2001003007349234884.71-7349

7349

JC-INV2001008928/05/2020INVOICE AGAINST SALE OF GOODS71300211887.717130

7130

RVRV20067713/10/2020CHQ NO: 6528-(12-OCT-20) MCR: 9312 Settled for: 2001008907130189134.71-7130

7130

JC-INV2001012627/07/2020INVOICE AGAINST SALE OF GOODS69210214500.7169216921
JC-INV2001009921/06/2020INVOICE AGAINST SALE OF GOODS61750178688.716175

6175

RVRV20071628/10/2020CHQ NO: 6550-(28-OCT-20) Settled for: 2001009906175182959.71-6175

6175

JC-INV2001008427/05/2020INVOICE AGAINST SALE OF GOODS52720174062.8152725272
JC-INV2001015912/09/2020INVOICE AGAINST SALE OF GOODS52000190166.7152005200
JC-INV2001001025/01/2020INVOICE AGAINST SALE OF GOODS51870278310.815187

5187

RVRV20032507/06/2020CHQ NO: 6086-(07-JUN-20) MCR: 9070 Settled for: 2001001005187161276.71-5187

5187

JC-INV2001017330/09/2020INVOICE AGAINST SALE OF GOODS49680194720.7149684968
JC-INV2001015631/08/2020INVOICE AGAINST SALE OF GOODS47270196406.7147274727
JC-INV2001005825/04/2020INVOICE AGAINST SALE OF GOODS42120311963.814212

4212

RVRV20057613/09/2020CHQ NO: 6382-(11-SEP-20) MCR: 9179 Settled for: 2001005804212203568.71-4212

4212

JC-INV2001007230/04/2020INVOICE AGAINST SALE OF GOODS38160259291.8138163816
JC-INV2001001931/01/2020INVOICE AGAINST SALE OF GOODS34410273445.813441

3441

RVRV20031201/06/2020CHQ NO: 6060-(26-MAY-20) MCR: 9064 Settled for: 2001001903441166463.71-3441

3441

JC-INV2001012427/07/2020INVOICE AGAINST SALE OF GOODS33280205643.7133283328
JC-INV2001009130/05/2020INVOICE AGAINST SALE OF GOODS19440213831.711944

1944

JC-INV2001009721/06/2020INVOICE AGAINST SALE OF GOODS19440164220.711944

1944

JC-INV2001012527/07/2020INVOICE AGAINST SALE OF GOODS19360207579.7119361936
JC-INV2001007616/05/2020INVOICE AGAINST SALE OF GOODS18470198737.8118471847

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.

gridlines

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