Bank Reconciliation with the help of VBA

vinukk4

New Member
Joined
Nov 10, 2020
Messages
2
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Hai,

I have a file with more than 6000 transcation monthly. I wanted to do reconciliation now i do manually. it tooks me more than one week to finish. Could any one help me to do this with the belp of VBA. I have attached the image. I have several cheques with same cheque number. Ususally if i deposit a cheque in bank it clear within 2 days. If any problem i can sort it with in two day. Could any one help me. Please.. Thanks.

General LedgerBAnk
DateChq No.DebitCreditDateChq No.s/ ParticularsDebitCredit
26/01/20​
OB31,313,295.6526/01/2020OB36,253,089.61
26/01/20​
0003165,000.0026/01/2020011172800,000.00
26/01/20​
000174330,000.0026/01/2020011173240,000.00
26/01/20​
CASH147,565.5026/01/20200112295,916.44
26/01/20​
SUB FEE210.0026/01/20200112309,192.75
26/01/20​
0112597,991.1026/01/2020CASH100,000.00
26/01/20​
01125422,522.8126/01/2020CASH47,565.50
26/01/20​
01125257,858.3926/01/2020Charges200.00
26/01/20​
0000847,500.0026/01/2020Charges10.00
26/01/20​
0000447,360.0026/01/2020Inward4,750.00
26/01/20​
0000274,000.0026/01/20200000013,166.00
26/01/20​
1000075,000.0026/01/20200000025,800.00
26/01/20​
00005914,500.0026/01/202000000224,187.50
26/01/20​
95808914,500.0026/01/20200000028,000.00
26/01/20​
5000578,250.0026/01/202000000458,487.00
26/01/20​
9542016,000.0026/01/20200000048,750.00
26/01/20​
00001521,250.0026/01/20200000046,300.00
26/01/20​
000020C2,100.0026/01/20200000055,125.00
26/01/20​
000020B200.0026/01/20200000054,500.00
26/01/20​
000020A10,500.0026/01/20200000056,000.00
26/01/20​
00017610,000.0026/01/20200000065,750.00
26/01/20​
0062243,500.0026/01/202000001110,500.00
26/01/20​
00622317,500.0026/01/20200000118,750.00
26/01/20​
00622117,500.0026/01/202000001315,750.00
26/01/20​
00622217,500.0026/01/20200000165,700.00
26/01/20​
0000222,800.0026/01/202000002012,000.00
26/01/20​
279388B450.0026/01/202000002011,250.00
26/01/20​
279388A9,000.0026/01/20200000212,400.00
26/01/20​
0000409,500.0026/01/20200000231,050.00
26/01/20​
10004512,500.0026/01/202000002314,500.00
26/01/20​
000041200.0026/01/20200000242,500.00
26/01/20​
0000385,500.0026/01/20200000245,500.00
26/01/20​
0062253,500.0026/01/20200000266,875.00
26/01/20​
0062263,500.0026/01/20200000364,300.00
26/01/20​
0000026,200.0026/01/202000003711,250.00
26/01/20​
000002B2,700.0026/01/20200000392,000.00
26/01/20​
000002A54,000.0026/01/202000004022,500.00
26/01/20​
6788701,200.0026/01/20200000449,000.00
26/01/20​
1671241,300.0026/01/20200000466,000.00
26/01/20​
0000281,150.0026/01/202000005013,250.00
26/01/20​
0039025,850.0026/01/20200000507,500.00
26/01/20​
0000463,250.0026/01/20200000505,835.00
26/01/20​
0002263,024.0026/01/202000005413,750.00
26/01/20​
191413200.0026/01/202000005514,750.00
26/01/20​
19141413,324.5026/01/202000005613,750.00
26/01/20​
000001B250.0026/01/20200000646,500.00
26/01/20​
000001C1,890.0026/01/20200000677,333.00
26/01/20​
000115B200.0026/01/20200000723,000.00
26/01/20​
000017200.0026/01/20200000725,000.00
26/01/20​
088974B200.0026/01/20200000737,000.00
26/01/20​
0001302,888.0026/01/20200000798,000.00
26/01/20​
2689525,000.0026/01/20200000912,000.00
26/01/20​
268954A3,600.0026/01/20200000997,500.00
26/01/20​
268954B3,600.0026/01/202000010314,500.00
26/01/20​
10008821,250.0026/01/20200001199,800.00
26/01/20​
000031A7,934.0026/01/20200001193,000.00
26/01/20​
26894818,000.0026/01/20200001388,000.00
26/01/20​
07011514,500.0026/01/20200001752,150.00
26/01/20​
088974A17,500.0026/01/20200002115,500.00
26/01/20​
000115A16,000.0026/01/202000021725,900.00
26/01/20​
50012619,800.0026/01/20200002277,350.00
26/01/20​
000001A6,000.0026/01/20200002535,500.00
26/01/20​
00004510,000.0026/01/202000025922,500.00
26/01/20​
00004320,000.0026/01/202000034612,096.00
26/01/20​
088974C1,800.0026/01/202000038411,250.00
26/01/20​
0000193,750.0026/01/20200007689,100.00
26/01/20​
000031B2,400.0026/01/202000077323,333.33
26/01/20​
0000275,750.0026/01/20200007979,750.00
26/01/20​
00390129,250.0026/01/202000088210,584.00
26/01/20​
00018815,750.0026/01/202000089011,250.00
26/01/20​
1671256,500.0026/01/20200009901,750.00
26/01/20​
6788676,000.0026/01/202000150514,250.00
26/01/20​
00298821,250.0026/01/20200016806,250.00
26/01/20​
00003910,000.0026/01/20200017201,800.00
27/01/20​
CASH28,897.7526/01/20200017433,400.00
27/01/20​
01124019,000.0026/01/202000222510,000.00
27/01/20​
0112412,000.0026/01/20200031152,415.00
27/01/20​
0112556,720.0026/01/20200046091,800.00
27/01/20​
0112614,095.0026/01/2020004836100,000.00
27/01/20​
0112485,250.0026/01/2020015827111,000.00
27/01/20​
01125824,150.0026/01/202002571018,667.00
27/01/20​
01125115,540.0026/01/202008260115,800.00
27/01/20​
0112769,240.0026/01/20201000037,500.00
27/01/20​
01125775,692.0026/01/20201000045,000.00
27/01/20​
01125331,899.0026/01/20201000466,000.00
27/01/20​
0000569,169.0026/01/202010004611,250.00
27/01/20​
00000612,000.0026/01/202010004730,000.00
27/01/20​
00007912,750.0026/01/20201000689,700.00
27/01/20​
17681933,750.0026/01/20201001044,400.00
27/01/20​
0000056,600.0026/01/20201001407,500.00
27/01/20​
00000213,750.0026/01/20201001799,000.00
27/01/20​
00000215,750.0026/01/202013896515,750.00
27/01/20​
00000913,500.0026/01/20201702193,700.00
27/01/20​
0000057,500.0026/01/20202000306,850.00
27/01/20​
50000310,800.0026/01/202020021121,250.00
27/01/20​
00311122,500.0026/01/20202115932,750.00
27/01/20​
00311027,000.0026/01/202029179513,050.00
27/01/20​
00006617,000.0026/01/202034380413,000.00
27/01/20​
98402016,250.0026/01/202036168231,500.00
27/01/20​
921003B787.5026/01/202036783613,500.00
27/01/20​
921003A10,500.0026/01/202037112913,000.00
27/01/20​
00003912,500.0026/01/202049538416,250.00
27/01/20​
000527B375.0026/01/202049992112,500.00
27/01/20​
000527A7,500.0026/01/20205004578,625.00
27/01/20​
0000819,200.0026/01/202053984626,450.00
27/01/20​
0001208,380.0026/01/20205588646,500.00
27/01/20​
00371452,500.0026/01/202059411112,250.00
27/01/20​
00013323,750.0026/01/20206284972,600.00
27/01/20​
2022904,250.0026/01/20206789595,666.00
27/01/20​
0000022,000.0026/01/20207228284,725.00
27/01/20​
0000512,415.0026/01/20207839416,750.00
27/01/20​
0000035,500.0026/01/202081358820,000.00
27/01/20​
100001B200.0026/01/20208329816,700.00
27/01/20​
100001D1,995.0026/01/202083551011,250.00
27/01/20​
100001C2,000.0026/01/202085275214,250.00
27/01/20​
0000341,900.0026/01/202093166313,500.00
27/01/20​
000012C950.0026/01/202000000224,187.50
27/01/20​
000012B1,050.0026/01/20200000055,125.00
27/01/20​
000012A2,000.0026/01/20200000245,500.00
27/01/20​
000019200.0026/01/20200000392,000.00
27/01/20​
100006200.0026/01/20200000798,000.00
27/01/20​
0014821,350.0026/01/202010004730,000.00
27/01/20​
00004114,700.0026/01/2020SDM Dep2,700.00
27/01/20​
5098443,255.0026/01/2020Transfer1,470.00
27/01/20​
666223A200.0026/01/202000030814,000.00
27/01/20​
666223B2,993.0026/01/202000011812,500.00
27/01/20​
0014786,750.0026/01/202000023610,500.00
27/01/20​
00005213,000.0026/01/20200000869,800.00
27/01/20​
10000521,250.0026/01/202000008015,750.00
27/01/20​
00017228,750.0026/01/202000358710,000.00
27/01/20​
0000134,750.0026/01/202000007511,666.65
27/01/20​
0000359,500.0026/01/202000072010,500.00
27/01/20​
00002412,250.0026/01/202000717813,750.00
27/01/20​
00003610,000.0026/01/202000015412,500.00
27/01/20​
100001A9,500.0026/01/202000003414,250.00
27/01/20​
00572821,250.0026/01/20200000119,200.00
27/01/20​
00002110,800.0026/01/20200001226,000.00
27/01/20​
00003210,700.0026/01/202000053721,250.00
27/01/20​
97 B0901,470.0026/01/202000114011,000.00
27/01/20​
53167 B3,600.0026/01/20200011412,200.00
28/01/20​
00003512,125.0026/01/20200000117,800.00
28/01/20​
00003314,500.0026/01/202000005614,500.00
28/01/20​
0000392,000.0026/01/202000003826,250.00
28/01/20​
0000055,125.0026/01/20200010074,000.00
28/01/20​
10004730,000.0026/01/202000000211,000.00
28/01/20​
0000245,500.0026/01/202000005610,200.00
28/01/20​
00000224,187.5026/01/20200002145,750.00
28/01/20​
0000798,000.0026/01/20200000228,750.00
28/01/20​
CASH68,412.0026/01/20200000568,320.00
28/01/20​
BANK CHG105.0026/01/20200000125,650.00
28/01/20​
BANK CHG4.2026/01/20200000129,500.00
28/01/20​
011256111,755.7026/01/20200000338,800.00
28/01/20​
01126024,265.5026/01/20200000046,650.00
28/01/20​
011281116,200.2526/01/20200000016,500.00
28/01/20​
011285200,000.0026/01/20200000673,000.00
28/01/20​
0000125,000.0026/01/20200000129,500.00
28/01/20​
0000387,000.0026/01/20200000481,350.00
28/01/20​
0000829,750.0026/01/202000001012,500.00
28/01/20​
00000611,400.0026/01/202000000213,000.00
28/01/20​
0046302,725.0026/01/202000004110,800.00
28/01/20​
1000054,150.0026/01/202000000110,800.00
28/01/20​
00001410,750.0026/01/20200000167,700.00
28/01/20​
0002175,500.0026/01/202000002820,000.00
28/01/20​
0000045,500.0026/01/20200000072,900.00
28/01/20​
00014813,500.0026/01/202000000112,000.00
28/01/20​
00000510,750.0026/01/202000007910,500.00
28/01/20​
0000154,000.0026/01/20200003097,300.00
28/01/20​
0000128,000.0027/01/20200112385,985.00
28/01/20​
0000246,835.0027/01/20200112496,600.00
28/01/20​
1000099,300.0027/01/202001125257,858.39
28/01/20​
00000213,500.0027/01/202001125422,522.81
28/01/20​
1000224,750.0027/01/2020CASH28,897.75
28/01/20​
00003713,750.0027/01/20200112597,991.00
28/01/20​
00003114,000.0027/01/20200112597,991.00
28/01/20​
0000078,350.0027/01/20200000018,140.00
28/01/20​
83726114,200.0027/01/20200000026,200.00
28/01/20​
00000618,000.0027/01/20200000193,750.00
28/01/20​
000010B300.0027/01/202000002012,800.00
28/01/20​
000010A6,000.0027/01/20200000222,800.00
28/01/20​
00015315,750.0027/01/20200000275,750.00
28/01/20​
5000599,330.0027/01/20200000274,000.00
28/01/20​
0000057,800.0027/01/20200000281,150.00
28/01/20​
0000388,750.0027/01/202000003910,000.00
28/01/20​
0000654,750.0027/01/202000018815,750.00
28/01/20​
00014212,500.0027/01/20200002263,024.00
28/01/20​
98491920,000.0027/01/202000298821,250.00
28/01/20​
0000395,800.0027/01/202000390129,250.00
28/01/20​
0000536,600.0027/01/20200039025,850.00
28/01/20​
500056B1,250.0027/01/202007011514,500.00
28/01/20​
500056A6,250.0027/01/202008897419,500.00
28/01/20​
00010515,000.0027/01/20201000075,000.00
28/01/20​
00000521,250.0027/01/202010004512,500.00
28/01/20​
0000841,100.0027/01/202010008821,250.00
28/01/20​
0000074,800.0027/01/20201671241,300.00
28/01/20​
00000813,250.0027/01/20201671256,500.00
28/01/20​
00008110,000.0027/01/2020191413200.00
28/01/20​
000036B200.0027/01/202019141413,324.50
28/01/20​
000036A13,000.0027/01/202026894818,000.00
28/01/20​
28772511,000.0027/01/20202689525,000.00
28/01/20​
000015A5,340.0027/01/20202689547,200.00
28/01/20​
0000153,000.0027/01/20202793889,450.00
28/01/20​
1000064,500.0027/01/20205000578,250.00
28/01/20​
00128114,000.0027/01/202050012619,800.00
28/01/20​
98395010,000.0027/01/20206788676,000.00
28/01/20​
000070B3,622.0027/01/20206788701,200.00
28/01/20​
0002211,800.0027/01/20209542016,000.00
28/01/20​
0001611,787.0027/01/202095808914,500.00
28/01/20​
011780C2,800.0027/01/20201000075,000.00
28/01/20​
011780B200.0027/01/20200001302,888.00
28/01/20​
011780D2,940.0027/01/20200014786,750.00
28/01/20​
0000912,520.0027/01/20200014821,350.00
28/01/20​
011780A14,000.0027/01/20200000409,500.00
28/01/20​
0029796,600.0027/01/202000005914,500.00
28/01/20​
00005115,000.0027/01/202000003110,334.00
28/01/20​
0000079,000.0027/01/202000622117,500.00
28/01/20​
00008124,075.0027/01/202000622217,500.00
28/01/20​
0000044,493.0027/01/202000622317,500.00
28/01/20​
0001467,500.0027/01/20200062243,500.00
28/01/20​
00929210,833.0027/01/20200062253,500.00
28/01/20​
000070A18,150.0027/01/20200062263,500.00
28/01/20​
00110710,500.0027/01/202000017610,000.00
28/01/20​
00002517,500.0027/01/202000006617,000.00
28/01/20​
0000036,000.0027/01/202000004510,000.00
28/01/20​
0000457,000.0027/01/202000017228,750.00
28/01/20​
5001907,700.0027/01/20200000341,900.00
28/01/20​
R532012,700.0027/01/20200000359,500.00
29/01/20​
CASH202,280.2527/01/202000004320,000.00
29/01/20​
TELLER10.5027/01/20200000463,250.00
29/01/20​
SUB FEE105.0027/01/2020000017200.00
29/01/20​
SALARY307,326.0027/01/20200000057,500.00
29/01/20​
SALARY286,057.0027/01/202000000612,000.00
29/01/20​
01122413,051.5027/01/20200000447,360.00
29/01/20​
011286250,000.0027/01/20200000124,000.00
29/01/20​
011180350,000.0027/01/20200000134,750.00
29/01/20​
0000023,500.0027/01/202000000215,750.00
29/01/20​
0001247,200.0027/01/202000000256,700.00
29/01/20​
0000128,200.0027/01/202000001521,250.00
29/01/20​
1000324,000.0027/01/20200000847,500.00
29/01/20​
1000046,700.0027/01/202000011516,200.00
29/01/20​
10002210,000.0027/01/202000000913,500.00
29/01/20​
1000608,400.0028/01/202001108836,515.00
29/01/20​
00010414,500.0028/01/202001124019,000.00
29/01/20​
21581816,250.0028/01/20200112412,000.00
29/01/20​
00572421,250.0028/01/2020011243675,000.00
29/01/20​
355949B1,750.0028/01/20200112485,250.00
29/01/20​
0001362,700.0028/01/20200112556,720.00
29/01/20​
000137200.0028/01/202001125824,150.00
29/01/20​
0000012,000.0028/01/20200112597,991.10
29/01/20​
0079113,402.0028/01/20200112614,095.00
29/01/20​
000247B2,850.0028/01/20200112769,240.00
29/01/20​
000022200.0028/01/2020011285200,000.00
29/01/20​
000218A200.0028/01/2020CASH68,412.00
29/01/20​
000218B3,675.0028/01/2020Charges5.00
29/01/20​
0008151,575.0028/01/2020Charges100.00
29/01/20​
00156032,400.0028/01/2020Charges0.20
29/01/20​
00156429,700.0028/01/2020Charges4.00
29/01/20​
355949A5,800.0028/01/202001125115,540.00
29/01/20​
000247A14,250.0028/01/202001125331,899.00
29/01/20​
00013213,500.0028/01/202000000213,750.00
29/01/20​
53413 B50.0028/01/20200000022,000.00
30/01/20​
CASH70,497.7528/01/20200000035,500.00
30/01/20​
01128742,236.6028/01/20200000056,600.00
30/01/20​
0000067,333.0028/01/2020000019200.00
30/01/20​
1000223,900.0028/01/202000002412,250.00
30/01/20​
0000065,500.0028/01/202000003210,700.00
30/01/20​
0000085,833.0028/01/202000003610,000.00
30/01/20​
00003611,250.0028/01/202000004114,700.00
30/01/20​
00005516,250.0028/01/20200000512,415.00
30/01/20​
0001639,750.0028/01/202000005213,000.00
30/01/20​
0000586,750.0028/01/20200000569,169.00
30/01/20​
0000645,800.0028/01/202000007912,750.00
30/01/20​
0012059,000.0028/01/20200000819,200.00
30/01/20​
5546328,300.0028/01/20200001208,380.00
30/01/20​
8029074,300.0028/01/202000013323,750.00
30/01/20​
00038410,000.0028/01/20200005277,875.00
30/01/20​
10001810,600.0028/01/202000311027,000.00
30/01/20​
0000357,000.0028/01/202000311122,500.00
30/01/20​
1000087,600.0028/01/202000371452,500.00
30/01/20​
00000315,000.0028/01/202000572821,250.00
30/01/20​
50079510,750.0028/01/202010000113,695.00
30/01/20​
0003256,250.0028/01/202010000521,250.00
30/01/20​
0000128,300.0028/01/2020100006200.00
30/01/20​
1000098,600.0028/01/202017681933,750.00
30/01/20​
0000097,834.0028/01/20202022904,250.00
30/01/20​
00439010,000.0028/01/202050000310,800.00
30/01/20​
00001112,500.0028/01/20205098443,255.00
30/01/20​
74115618,000.0028/01/20206662233,193.00
30/01/20​
0000098,300.0028/01/202092100311,287.50
30/01/20​
00006426,250.0028/01/202098402016,250.00
30/01/20​
0000898,300.0028/01/202000002110,800.00
30/01/20​
8051465,000.0028/01/202000000213,750.00
30/01/20​
00001712,500.0028/01/2020SDM Dep3,000.00
30/01/20​
0000565,000.0028/01/2020SDM Dep50.00
30/01/20​
0000278,700.0028/01/20200001467,500.00
30/01/20​
0000185,500.0028/01/20200000387,000.00
30/01/20​
3021595,000.0028/01/202000003613,200.00
30/01/20​
0000027,500.0028/01/20200000841,100.00
30/01/20​
00038411,421.0028/01/20200000654,750.00
30/01/20​
0000047,500.0028/01/20200001611,787.00
30/01/20​
00001215,000.0028/01/202000003915,000.00
30/01/20​
0000516,500.0028/01/202000000611,400.00
30/01/20​
000142B200.0028/01/202000008110,000.00
30/01/20​
000142A9,500.0028/01/20200000079,000.00
30/01/20​
0029092,360.0028/01/202000110710,500.00
30/01/20​
000016B200.0028/01/202000003713,750.00
30/01/20​
000016A11,250.0028/01/202000010515,000.00
30/01/20​
0000105,000.0028/01/20200000125,000.00
30/01/20​
1000219,500.0028/01/202000003912,500.00
30/01/20​
0003311,950.0028/01/20200000536,600.00
30/01/20​
000365B200.0028/01/202000001410,750.00
30/01/20​
000365A11,250.0028/01/202000007021,772.00
30/01/20​
0000163,500.0028/01/20200000044,493.00
30/01/20​
1000056,300.0028/01/20200000078,350.00
30/01/20​
119528B200.0028/01/202000000521,250.00
30/01/20​
119528A18,800.0028/01/202000002517,500.00
30/01/20​
0767273,500.0028/01/2020Salary307,326.00
30/01/20​
0000148,750.0029/01/2020011180350,000.00
30/01/20​
00175221,000.0029/01/2020011256111,755.70
30/01/20​
854243B100.0029/01/202001125775,692.00
30/01/20​
0000049,750.0029/01/202001126024,265.50
30/01/20​
00012425,000.0029/01/2020CASH102,280.25
30/01/20​
0003103,600.0029/01/2020CASH100,000.00
30/01/20​
000018B200.0029/01/2020Charges0.50
30/01/20​
000012B200.0029/01/2020Charges10.00
30/01/20​
000018A15,250.0029/01/2020Charges5.00
30/01/20​
000012A15,250.0029/01/2020Charges100.00
30/01/20​
00030915,750.0029/01/2020011286250,000.00
30/01/20​
00002110,000.0029/01/202000000213,500.00
30/01/20​
548980B200.0029/01/20200000036,000.00
30/01/20​
9621509,000.0029/01/20200000045,500.00
30/01/20​
548980A13,000.0029/01/202000000510,750.00
30/01/20​
00802817,500.0029/01/20200000057,800.00
30/01/20​
0001229,600.0029/01/202000000618,000.00
30/01/20​
0000172,000.0029/01/20200000074,800.00
30/01/20​
0000159,500.0029/01/202000000813,250.00
30/01/20​
16534911,250.0029/01/20200000106,300.00
30/01/20​
50005116,250.0029/01/20200000128,000.00
30/01/20​
00006416,250.0029/01/20200000153,000.00
30/01/20​
00005913,750.0029/01/20200000154,000.00
30/01/20​
0001412,500.0029/01/20200000155,340.00
30/01/20​
2007353,750.0029/01/20200000246,835.00
30/01/20​
00008410,700.0029/01/202000003114,000.00
30/01/20​
48758011,000.0029/01/20200000388,750.00
30/01/20​
20073418,750.0029/01/20200000395,800.00
30/01/20​
0001735,750.0029/01/202000005115,000.00
30/01/20​
100041B800.0029/01/202000008124,075.00
30/01/20​
100041A16,000.0029/01/20200000829,750.00
30/01/20​
00014810,300.0029/01/20200000912,520.00
30/01/20​
017807924,000.0029/01/202000014813,500.00
30/01/20​
000098B200.0029/01/20200002175,500.00
30/01/20​
000098D2,835.0029/01/20200002211,800.00
30/01/20​
000098C2,700.0029/01/202000128114,000.00
30/01/20​
000010200.0029/01/20200029796,600.00
30/01/20​
377212B200.0029/01/20200046302,725.00
30/01/20​
000025B2,600.0029/01/202000929210,833.00
30/01/20​
000025A13,000.0029/01/202001178019,940.00
30/01/20​
377212A5,000.0029/01/20201000054,150.00
30/01/20​
50002611,500.0029/01/20201000064,500.00
30/01/20​
000098A9,000.0029/01/20201000099,300.00
30/01/20​
1001376,904.0029/01/20201000224,750.00
30/01/20​
00022521,000.0029/01/202028772511,000.00
30/01/20​
854243A5,400.0029/01/20205000567,500.00
30/01/20​
10001610,200.0029/01/20205000599,330.00
30/01/20​
00000224,187.5029/01/20205001907,700.00
30/01/20​
10004730,000.0029/01/202083726114,200.00
30/01/20​
AL STLM820.0029/01/202098395010,000.00
31/01/20​
TELLER FEE10.5029/01/202098491920,000.00
31/01/20​
cb33,201,554.8529/01/20200000457,000.00
35,621,288.4035,621,288.4029/01/202000000213,500.00
-29/01/202000000618,000.00
29/01/202000000813,250.00
29/01/20200000128,000.00
29/01/20200000246,835.00
29/01/20200000457,000.00
29/01/202000010414,500.00
29/01/20200000385,500.00
29/01/2020000041200.00
29/01/202000014212,500.00
29/01/20200079113,402.00
29/01/202000015315,750.00
29/01/2020TRF500.00
29/01/20200000385,500.00
29/01/2020000041200.00
29/01/2020Salary286,057.00
30/01/2020011162800,000.00
30/01/2020011167800,000.00
30/01/202001125049,077.00
30/01/2020011275109,425.75
30/01/2020BANK TRF200.00
30/01/2020BANK TRF15,700.00
30/01/2020BANK TRF1,940.00
30/01/2020CASH1,370.00
30/01/2020CASH820.00
30/01/2020CASH70,497.75
30/01/202001128742,236.60
30/01/20200000012,000.00
30/01/20200000023,500.00
30/01/20200000128,200.00
30/01/2020000022200.00
30/01/20200001247,200.00
30/01/202000013213,500.00
30/01/20200001362,700.00
30/01/2020000137200.00
30/01/20200002183,875.00
30/01/202000024717,100.00
30/01/20200008151,575.00
30/01/202000156032,400.00
30/01/202000156429,700.00
30/01/202000572421,250.00
30/01/20201000046,700.00
30/01/202010002210,000.00
30/01/20201000324,000.00
30/01/20201000608,400.00
30/01/202021581816,250.00
30/01/20203559497,550.00
30/01/202010002210,000.00
31/01/2020CB34,912,487.60
31/01/2020Transfer10.00
31/01/2020Sal Charges0.50
40,488,082.3440,488,082.34
-
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

vinukk4

New Member
Joined
Nov 10, 2020
Messages
2
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Hai,

I have a file with more than 6000 transcation monthly. I wanted to do reconciliation with bank and ledger. Now i do it manually. It tooks me more than one week to finish. Could any one help me to do this with the help of VBA. I have several cheques with same cheque number. Ususally if i deposit a cheque in bank it clear within 2 days. Could any one help me to sort out this issue.. Humble request Please.. Thanks.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,499
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Some ideas

  • Work out a simple process that works
  • Clean up the data
  • Ensure you have unique reference numbers
  • Simplify data - use one column instead of 2
  • Try the formulas to ensure that the process works
  • Search for tools that may help; a quick search showed several with potential
    • Add-ins
    • Excel Power Query


N.B. Many accounting programs include a reconciliation component.

A concise example that uses 2 Tables follows.
To secure a list of outstanding items filter on the Cleared column 0.

B2 can be =SUMPRODUCT((GL[Reference]<>"")/COUNTIF(GL[Reference],GL[Reference]&""))=COUNTA(GL[Reference])

Bank Rec 2020.xlsm
ABCDEF
1Total of visible entries882.50
2References UniqueTRUE
3DateDescriptionReferenceAmountBalanceCleared
401-Mar-20Dep2020.1500.00 500.00 02-Mar-20
505-Mar-2012(15.00)485.00 07-Mar-20
610-Mar-2014(100.00)385.00 11-Mar-20
715-Mar-20sc202003a(2.50)382.50 16-Mar-20
831-Mar-20Dep2020.21,000.00 1,382.50 31-Mar-20
931-Mar-20Dep2020.38,000.00 9,382.50 31-Mar-20
1031-Mar-2015(5,000.00)4,382.50  
1131-Mar-2016(3,500.00)882.50  
12
GL
Cell Formulas
RangeFormula
D1D1=SUBTOTAL(109,GL[Amount])
B2B2=SUMPRODUCT((C1:C5000<>"")/COUNTIF(C1:C5000,C1:C5000&""))=COUNTA(C:C)
E4:E11E4=SUM($D$4:D4)
F4:F11F4=SUMPRODUCT(--(BK[Reference]=[@Reference]),--(BK[Amount]=[@Amount]),BK[Date])
 
Last edited:

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,499
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Bank Rec 2020.xlsm
ABCDEF
1Total of visible entries9,377.50
2References uniqueTRUE
3DateDescriptionReferenceAmountBalanceCleared
402-Mar-20Dep2020.1500.00 500.00 TRUE
507-Mar-2012(15.00)485.00 TRUE
611-Mar-2014(100.00)385.00 TRUE
716-Mar-20sc202003a(2.50)382.50 TRUE
831-Mar-20sc202003b(5.00)377.50 FALSE
931-Mar-20Dep2020.21,000.00 1,377.50 TRUE
1031-Mar-20Dep2020.38,000.00 9,377.50 TRUE
11
Bank
Cell Formulas
RangeFormula
D1D1=SUBTOTAL(109,BK[Amount])
B2B2=SUMPRODUCT((C1:C5000<>"")/COUNTIF(C1:C5000,C1:C5000&""))=COUNTA(C:C)
E4:E10E4=SUM($D$4:D4)
F4:F10F4=SUMPRODUCT(--(GL[Reference]=[@Reference]),--(GL[Amount]=[@Amount]))=1



Bank Rec 2020.xlsm
BCD
1Bank Reconciliation31-Mar-20
2
3Bank Statement - Balance9,377.50
4Outstanding entries(8,500.00)
5Unrecorded bank entries5.00
6Balance882.50
7GL - Balance882.50
8Diffference0.00 J
9
10D7 Has Wingdings font
11
Rec
Cell Formulas
RangeFormula
D1D1=LOOKUP(BigNum,(GL[Date]))
C3C3=SUM(BK[Amount])
C4C4=SUMIFS(GL[Amount],GL[Cleared],0)
C5C5=-SUMIFS(BK[Amount],BK[Cleared],FALSE)
C6C6=SUM(C3:C5)
C7C7=SUM(GL[Amount])
C8C8=C6-C7
D8D8=IF(C8=0, "J","L")



With both the GL and the Bank, the Dr and Cr columns are combined.
To prepare a list of outstanding or unrecorded items, just filter on the cleared column.
The total of the visible entries shows at the top of the sheet.
The GL Cleared column shows the date the entry cleared the bank.
D7 Has Wingdings font (Smile icon)

B2 can be =SUMPRODUCT((GL[Reference]<>"")/COUNTIF(GL[Reference],GL[Reference]&""))=COUNTA(GL[Reference])
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,118,765
Messages
5,574,116
Members
412,572
Latest member
Chriszion
Top