Bank Reconciliation with the help of VBA

[vinukk4]

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 Ledger					BAnk
Date	Chq No.	Debit	Credit		Date	Chq No.s/ Particulars	Debit	Credit
26/01/20​	OB	31,313,295.65			26/01/2020	OB		36,253,089.61
26/01/20​	000316		5,000.00		26/01/2020	011172	800,000.00
26/01/20​	000174		330,000.00		26/01/2020	011173	240,000.00
26/01/20​	CASH	147,565.50			26/01/2020	011229	5,916.44
26/01/20​	SUB FEE		210.00		26/01/2020	011230	9,192.75
26/01/20​	011259		7,991.10		26/01/2020	CASH		100,000.00
26/01/20​	011254		22,522.81		26/01/2020	CASH		47,565.50
26/01/20​	011252		57,858.39		26/01/2020	Charges	200.00
26/01/20​	000084	7,500.00			26/01/2020	Charges	10.00
26/01/20​	000044	7,360.00			26/01/2020	Inward		4,750.00
26/01/20​	000027	4,000.00			26/01/2020	000001		3,166.00
26/01/20​	100007	5,000.00			26/01/2020	000002		5,800.00
26/01/20​	000059	14,500.00			26/01/2020	000002		24,187.50
26/01/20​	958089	14,500.00			26/01/2020	000002		8,000.00
26/01/20​	500057	8,250.00			26/01/2020	000004		58,487.00
26/01/20​	954201	6,000.00			26/01/2020	000004		8,750.00
26/01/20​	000015	21,250.00			26/01/2020	000004		6,300.00
26/01/20​	000020C	2,100.00			26/01/2020	000005		5,125.00
26/01/20​	000020B	200.00			26/01/2020	000005		4,500.00
26/01/20​	000020A	10,500.00			26/01/2020	000005		6,000.00
26/01/20​	000176	10,000.00			26/01/2020	000006		5,750.00
26/01/20​	006224	3,500.00			26/01/2020	000011		10,500.00
26/01/20​	006223	17,500.00			26/01/2020	000011		8,750.00
26/01/20​	006221	17,500.00			26/01/2020	000013		15,750.00
26/01/20​	006222	17,500.00			26/01/2020	000016		5,700.00
26/01/20​	000022	2,800.00			26/01/2020	000020		12,000.00
26/01/20​	279388B	450.00			26/01/2020	000020		11,250.00
26/01/20​	279388A	9,000.00			26/01/2020	000021		2,400.00
26/01/20​	000040	9,500.00			26/01/2020	000023		1,050.00
26/01/20​	100045	12,500.00			26/01/2020	000023		14,500.00
26/01/20​	000041	200.00			26/01/2020	000024		2,500.00
26/01/20​	000038	5,500.00			26/01/2020	000024		5,500.00
26/01/20​	006225	3,500.00			26/01/2020	000026		6,875.00
26/01/20​	006226	3,500.00			26/01/2020	000036		4,300.00
26/01/20​	000002	6,200.00			26/01/2020	000037		11,250.00
26/01/20​	000002B	2,700.00			26/01/2020	000039		2,000.00
26/01/20​	000002A	54,000.00			26/01/2020	000040		22,500.00
26/01/20​	678870	1,200.00			26/01/2020	000044		9,000.00
26/01/20​	167124	1,300.00			26/01/2020	000046		6,000.00
26/01/20​	000028	1,150.00			26/01/2020	000050		13,250.00
26/01/20​	003902	5,850.00			26/01/2020	000050		7,500.00
26/01/20​	000046	3,250.00			26/01/2020	000050		5,835.00
26/01/20​	000226	3,024.00			26/01/2020	000054		13,750.00
26/01/20​	191413	200.00			26/01/2020	000055		14,750.00
26/01/20​	191414	13,324.50			26/01/2020	000056		13,750.00
26/01/20​	000001B	250.00			26/01/2020	000064		6,500.00
26/01/20​	000001C	1,890.00			26/01/2020	000067		7,333.00
26/01/20​	000115B	200.00			26/01/2020	000072		3,000.00
26/01/20​	000017	200.00			26/01/2020	000072		5,000.00
26/01/20​	088974B	200.00			26/01/2020	000073		7,000.00
26/01/20​	000130	2,888.00			26/01/2020	000079		8,000.00
26/01/20​	268952	5,000.00			26/01/2020	000091		2,000.00
26/01/20​	268954A	3,600.00			26/01/2020	000099		7,500.00
26/01/20​	268954B	3,600.00			26/01/2020	000103		14,500.00
26/01/20​	100088	21,250.00			26/01/2020	000119		9,800.00
26/01/20​	000031A	7,934.00			26/01/2020	000119		3,000.00
26/01/20​	268948	18,000.00			26/01/2020	000138		8,000.00
26/01/20​	070115	14,500.00			26/01/2020	000175		2,150.00
26/01/20​	088974A	17,500.00			26/01/2020	000211		5,500.00
26/01/20​	000115A	16,000.00			26/01/2020	000217		25,900.00
26/01/20​	500126	19,800.00			26/01/2020	000227		7,350.00
26/01/20​	000001A	6,000.00			26/01/2020	000253		5,500.00
26/01/20​	000045	10,000.00			26/01/2020	000259		22,500.00
26/01/20​	000043	20,000.00			26/01/2020	000346		12,096.00
26/01/20​	088974C	1,800.00			26/01/2020	000384		11,250.00
26/01/20​	000019	3,750.00			26/01/2020	000768		9,100.00
26/01/20​	000031B	2,400.00			26/01/2020	000773		23,333.33
26/01/20​	000027	5,750.00			26/01/2020	000797		9,750.00
26/01/20​	003901	29,250.00			26/01/2020	000882		10,584.00
26/01/20​	000188	15,750.00			26/01/2020	000890		11,250.00
26/01/20​	167125	6,500.00			26/01/2020	000990		1,750.00
26/01/20​	678867	6,000.00			26/01/2020	001505		14,250.00
26/01/20​	002988	21,250.00			26/01/2020	001680		6,250.00
26/01/20​	000039	10,000.00			26/01/2020	001720		1,800.00
27/01/20​	CASH	28,897.75			26/01/2020	001743		3,400.00
27/01/20​	011240		19,000.00		26/01/2020	002225		10,000.00
27/01/20​	011241		2,000.00		26/01/2020	003115		2,415.00
27/01/20​	011255		6,720.00		26/01/2020	004609		1,800.00
27/01/20​	011261		4,095.00		26/01/2020	004836		100,000.00
27/01/20​	011248		5,250.00		26/01/2020	015827		111,000.00
27/01/20​	011258		24,150.00		26/01/2020	025710		18,667.00
27/01/20​	011251		15,540.00		26/01/2020	082601		15,800.00
27/01/20​	011276		9,240.00		26/01/2020	100003		7,500.00
27/01/20​	011257		75,692.00		26/01/2020	100004		5,000.00
27/01/20​	011253		31,899.00		26/01/2020	100046		6,000.00
27/01/20​	000056	9,169.00			26/01/2020	100046		11,250.00
27/01/20​	000006	12,000.00			26/01/2020	100047		30,000.00
27/01/20​	000079	12,750.00			26/01/2020	100068		9,700.00
27/01/20​	176819	33,750.00			26/01/2020	100104		4,400.00
27/01/20​	000005	6,600.00			26/01/2020	100140		7,500.00
27/01/20​	000002	13,750.00			26/01/2020	100179		9,000.00
27/01/20​	000002	15,750.00			26/01/2020	138965		15,750.00
27/01/20​	000009	13,500.00			26/01/2020	170219		3,700.00
27/01/20​	000005	7,500.00			26/01/2020	200030		6,850.00
27/01/20​	500003	10,800.00			26/01/2020	200211		21,250.00
27/01/20​	003111	22,500.00			26/01/2020	211593		2,750.00
27/01/20​	003110	27,000.00			26/01/2020	291795		13,050.00
27/01/20​	000066	17,000.00			26/01/2020	343804		13,000.00
27/01/20​	984020	16,250.00			26/01/2020	361682		31,500.00
27/01/20​	921003B	787.50			26/01/2020	367836		13,500.00
27/01/20​	921003A	10,500.00			26/01/2020	371129		13,000.00
27/01/20​	000039	12,500.00			26/01/2020	495384		16,250.00
27/01/20​	000527B	375.00			26/01/2020	499921		12,500.00
27/01/20​	000527A	7,500.00			26/01/2020	500457		8,625.00
27/01/20​	000081	9,200.00			26/01/2020	539846		26,450.00
27/01/20​	000120	8,380.00			26/01/2020	558864		6,500.00
27/01/20​	003714	52,500.00			26/01/2020	594111		12,250.00
27/01/20​	000133	23,750.00			26/01/2020	628497		2,600.00
27/01/20​	202290	4,250.00			26/01/2020	678959		5,666.00
27/01/20​	000002	2,000.00			26/01/2020	722828		4,725.00
27/01/20​	000051	2,415.00			26/01/2020	783941		6,750.00
27/01/20​	000003	5,500.00			26/01/2020	813588		20,000.00
27/01/20​	100001B	200.00			26/01/2020	832981		6,700.00
27/01/20​	100001D	1,995.00			26/01/2020	835510		11,250.00
27/01/20​	100001C	2,000.00			26/01/2020	852752		14,250.00
27/01/20​	000034	1,900.00			26/01/2020	931663		13,500.00
27/01/20​	000012C	950.00			26/01/2020	000002	24,187.50
27/01/20​	000012B	1,050.00			26/01/2020	000005	5,125.00
27/01/20​	000012A	2,000.00			26/01/2020	000024	5,500.00
27/01/20​	000019	200.00			26/01/2020	000039	2,000.00
27/01/20​	100006	200.00			26/01/2020	000079	8,000.00
27/01/20​	001482	1,350.00			26/01/2020	100047	30,000.00
27/01/20​	000041	14,700.00			26/01/2020	SDM Dep		2,700.00
27/01/20​	509844	3,255.00			26/01/2020	Transfer		1,470.00
27/01/20​	666223A	200.00			26/01/2020	000308		14,000.00
27/01/20​	666223B	2,993.00			26/01/2020	000118		12,500.00
27/01/20​	001478	6,750.00			26/01/2020	000236		10,500.00
27/01/20​	000052	13,000.00			26/01/2020	000086		9,800.00
27/01/20​	100005	21,250.00			26/01/2020	000080		15,750.00
27/01/20​	000172	28,750.00			26/01/2020	003587		10,000.00
27/01/20​	000013	4,750.00			26/01/2020	000075		11,666.65
27/01/20​	000035	9,500.00			26/01/2020	000720		10,500.00
27/01/20​	000024	12,250.00			26/01/2020	007178		13,750.00
27/01/20​	000036	10,000.00			26/01/2020	000154		12,500.00
27/01/20​	100001A	9,500.00			26/01/2020	000034		14,250.00
27/01/20​	005728	21,250.00			26/01/2020	000011		9,200.00
27/01/20​	000021	10,800.00			26/01/2020	000122		6,000.00
27/01/20​	000032	10,700.00			26/01/2020	000537		21,250.00
27/01/20​	97 B090	1,470.00			26/01/2020	001140		11,000.00
27/01/20​	53167 B	3,600.00			26/01/2020	001141		2,200.00
28/01/20​	000035		12,125.00		26/01/2020	000011		7,800.00
28/01/20​	000033		14,500.00		26/01/2020	000056		14,500.00
28/01/20​	000039		2,000.00		26/01/2020	000038		26,250.00
28/01/20​	000005		5,125.00		26/01/2020	001007		4,000.00
28/01/20​	100047		30,000.00		26/01/2020	000002		11,000.00
28/01/20​	000024		5,500.00		26/01/2020	000056		10,200.00
28/01/20​	000002		24,187.50		26/01/2020	000214		5,750.00
28/01/20​	000079		8,000.00		26/01/2020	000022		8,750.00
28/01/20​	CASH	68,412.00			26/01/2020	000056		8,320.00
28/01/20​	BANK CHG		105.00		26/01/2020	000012		5,650.00
28/01/20​	BANK CHG		4.20		26/01/2020	000012		9,500.00
28/01/20​	011256		111,755.70		26/01/2020	000033		8,800.00
28/01/20​	011260		24,265.50		26/01/2020	000004		6,650.00
28/01/20​	011281		116,200.25		26/01/2020	000001		6,500.00
28/01/20​	011285		200,000.00		26/01/2020	000067		3,000.00
28/01/20​	000012	5,000.00			26/01/2020	000012		9,500.00
28/01/20​	000038	7,000.00			26/01/2020	000048		1,350.00
28/01/20​	000082	9,750.00			26/01/2020	000010		12,500.00
28/01/20​	000006	11,400.00			26/01/2020	000002		13,000.00
28/01/20​	004630	2,725.00			26/01/2020	000041		10,800.00
28/01/20​	100005	4,150.00			26/01/2020	000001		10,800.00
28/01/20​	000014	10,750.00			26/01/2020	000016		7,700.00
28/01/20​	000217	5,500.00			26/01/2020	000028		20,000.00
28/01/20​	000004	5,500.00			26/01/2020	000007		2,900.00
28/01/20​	000148	13,500.00			26/01/2020	000001		12,000.00
28/01/20​	000005	10,750.00			26/01/2020	000079		10,500.00
28/01/20​	000015	4,000.00			26/01/2020	000309		7,300.00
28/01/20​	000012	8,000.00			27/01/2020	011238	5,985.00
28/01/20​	000024	6,835.00			27/01/2020	011249	6,600.00
28/01/20​	100009	9,300.00			27/01/2020	011252	57,858.39
28/01/20​	000002	13,500.00			27/01/2020	011254	22,522.81
28/01/20​	100022	4,750.00			27/01/2020	CASH		28,897.75
28/01/20​	000037	13,750.00			27/01/2020	011259	7,991.00
28/01/20​	000031	14,000.00			27/01/2020	011259		7,991.00
28/01/20​	000007	8,350.00			27/01/2020	000001		8,140.00
28/01/20​	837261	14,200.00			27/01/2020	000002		6,200.00
28/01/20​	000006	18,000.00			27/01/2020	000019		3,750.00
28/01/20​	000010B	300.00			27/01/2020	000020		12,800.00
28/01/20​	000010A	6,000.00			27/01/2020	000022		2,800.00
28/01/20​	000153	15,750.00			27/01/2020	000027		5,750.00
28/01/20​	500059	9,330.00			27/01/2020	000027		4,000.00
28/01/20​	000005	7,800.00			27/01/2020	000028		1,150.00
28/01/20​	000038	8,750.00			27/01/2020	000039		10,000.00
28/01/20​	000065	4,750.00			27/01/2020	000188		15,750.00
28/01/20​	000142	12,500.00			27/01/2020	000226		3,024.00
28/01/20​	984919	20,000.00			27/01/2020	002988		21,250.00
28/01/20​	000039	5,800.00			27/01/2020	003901		29,250.00
28/01/20​	000053	6,600.00			27/01/2020	003902		5,850.00
28/01/20​	500056B	1,250.00			27/01/2020	070115		14,500.00
28/01/20​	500056A	6,250.00			27/01/2020	088974		19,500.00
28/01/20​	000105	15,000.00			27/01/2020	100007		5,000.00
28/01/20​	000005	21,250.00			27/01/2020	100045		12,500.00
28/01/20​	000084	1,100.00			27/01/2020	100088		21,250.00
28/01/20​	000007	4,800.00			27/01/2020	167124		1,300.00
28/01/20​	000008	13,250.00			27/01/2020	167125		6,500.00
28/01/20​	000081	10,000.00			27/01/2020	191413		200.00
28/01/20​	000036B	200.00			27/01/2020	191414		13,324.50
28/01/20​	000036A	13,000.00			27/01/2020	268948		18,000.00
28/01/20​	287725	11,000.00			27/01/2020	268952		5,000.00
28/01/20​	000015A	5,340.00			27/01/2020	268954		7,200.00
28/01/20​	000015	3,000.00			27/01/2020	279388		9,450.00
28/01/20​	100006	4,500.00			27/01/2020	500057		8,250.00
28/01/20​	001281	14,000.00			27/01/2020	500126		19,800.00
28/01/20​	983950	10,000.00			27/01/2020	678867		6,000.00
28/01/20​	000070B	3,622.00			27/01/2020	678870		1,200.00
28/01/20​	000221	1,800.00			27/01/2020	954201		6,000.00
28/01/20​	000161	1,787.00			27/01/2020	958089		14,500.00
28/01/20​	011780C	2,800.00			27/01/2020	100007	5,000.00
28/01/20​	011780B	200.00			27/01/2020	000130		2,888.00
28/01/20​	011780D	2,940.00			27/01/2020	001478		6,750.00
28/01/20​	000091	2,520.00			27/01/2020	001482		1,350.00
28/01/20​	011780A	14,000.00			27/01/2020	000040		9,500.00
28/01/20​	002979	6,600.00			27/01/2020	000059		14,500.00
28/01/20​	000051	15,000.00			27/01/2020	000031		10,334.00
28/01/20​	000007	9,000.00			27/01/2020	006221		17,500.00
28/01/20​	000081	24,075.00			27/01/2020	006222		17,500.00
28/01/20​	000004	4,493.00			27/01/2020	006223		17,500.00
28/01/20​	000146	7,500.00			27/01/2020	006224		3,500.00
28/01/20​	009292	10,833.00			27/01/2020	006225		3,500.00
28/01/20​	000070A	18,150.00			27/01/2020	006226		3,500.00
28/01/20​	001107	10,500.00			27/01/2020	000176		10,000.00
28/01/20​	000025	17,500.00			27/01/2020	000066		17,000.00
28/01/20​	000003	6,000.00			27/01/2020	000045		10,000.00
28/01/20​	000045	7,000.00			27/01/2020	000172		28,750.00
28/01/20​	500190	7,700.00			27/01/2020	000034		1,900.00
28/01/20​	R53201	2,700.00			27/01/2020	000035		9,500.00
29/01/20​	CASH	202,280.25			27/01/2020	000043		20,000.00
29/01/20​	TELLER		10.50		27/01/2020	000046		3,250.00
29/01/20​	SUB FEE		105.00		27/01/2020	000017		200.00
29/01/20​	SALARY		307,326.00		27/01/2020	000005		7,500.00
29/01/20​	SALARY		286,057.00		27/01/2020	000006		12,000.00
29/01/20​	011224		13,051.50		27/01/2020	000044		7,360.00
29/01/20​	011286		250,000.00		27/01/2020	000012		4,000.00
29/01/20​	011180		350,000.00		27/01/2020	000013		4,750.00
29/01/20​	000002	3,500.00			27/01/2020	000002		15,750.00
29/01/20​	000124	7,200.00			27/01/2020	000002		56,700.00
29/01/20​	000012	8,200.00			27/01/2020	000015		21,250.00
29/01/20​	100032	4,000.00			27/01/2020	000084		7,500.00
29/01/20​	100004	6,700.00			27/01/2020	000115		16,200.00
29/01/20​	100022	10,000.00			27/01/2020	000009		13,500.00
29/01/20​	100060	8,400.00			28/01/2020	011088	36,515.00
29/01/20​	000104	14,500.00			28/01/2020	011240	19,000.00
29/01/20​	215818	16,250.00			28/01/2020	011241	2,000.00
29/01/20​	005724	21,250.00			28/01/2020	011243	675,000.00
29/01/20​	355949B	1,750.00			28/01/2020	011248	5,250.00
29/01/20​	000136	2,700.00			28/01/2020	011255	6,720.00
29/01/20​	000137	200.00			28/01/2020	011258	24,150.00
29/01/20​	000001	2,000.00			28/01/2020	011259	7,991.10
29/01/20​	007911	3,402.00			28/01/2020	011261	4,095.00
29/01/20​	000247B	2,850.00			28/01/2020	011276	9,240.00
29/01/20​	000022	200.00			28/01/2020	011285	200,000.00
29/01/20​	000218A	200.00			28/01/2020	CASH		68,412.00
29/01/20​	000218B	3,675.00			28/01/2020	Charges	5.00
29/01/20​	000815	1,575.00			28/01/2020	Charges	100.00
29/01/20​	001560	32,400.00			28/01/2020	Charges	0.20
29/01/20​	001564	29,700.00			28/01/2020	Charges	4.00
29/01/20​	355949A	5,800.00			28/01/2020	011251	15,540.00
29/01/20​	000247A	14,250.00			28/01/2020	011253	31,899.00
29/01/20​	000132	13,500.00			28/01/2020	000002		13,750.00
29/01/20​	53413 B	50.00			28/01/2020	000002		2,000.00
30/01/20​	CASH	70,497.75			28/01/2020	000003		5,500.00
30/01/20​	011287		42,236.60		28/01/2020	000005		6,600.00
30/01/20​	000006	7,333.00			28/01/2020	000019		200.00
30/01/20​	100022	3,900.00			28/01/2020	000024		12,250.00
30/01/20​	000006	5,500.00			28/01/2020	000032		10,700.00
30/01/20​	000008	5,833.00			28/01/2020	000036		10,000.00
30/01/20​	000036	11,250.00			28/01/2020	000041		14,700.00
30/01/20​	000055	16,250.00			28/01/2020	000051		2,415.00
30/01/20​	000163	9,750.00			28/01/2020	000052		13,000.00
30/01/20​	000058	6,750.00			28/01/2020	000056		9,169.00
30/01/20​	000064	5,800.00			28/01/2020	000079		12,750.00
30/01/20​	001205	9,000.00			28/01/2020	000081		9,200.00
30/01/20​	554632	8,300.00			28/01/2020	000120		8,380.00
30/01/20​	802907	4,300.00			28/01/2020	000133		23,750.00
30/01/20​	000384	10,000.00			28/01/2020	000527		7,875.00
30/01/20​	100018	10,600.00			28/01/2020	003110		27,000.00
30/01/20​	000035	7,000.00			28/01/2020	003111		22,500.00
30/01/20​	100008	7,600.00			28/01/2020	003714		52,500.00
30/01/20​	000003	15,000.00			28/01/2020	005728		21,250.00
30/01/20​	500795	10,750.00			28/01/2020	100001		13,695.00
30/01/20​	000325	6,250.00			28/01/2020	100005		21,250.00
30/01/20​	000012	8,300.00			28/01/2020	100006		200.00
30/01/20​	100009	8,600.00			28/01/2020	176819		33,750.00
30/01/20​	000009	7,834.00			28/01/2020	202290		4,250.00
30/01/20​	004390	10,000.00			28/01/2020	500003		10,800.00
30/01/20​	000011	12,500.00			28/01/2020	509844		3,255.00
30/01/20​	741156	18,000.00			28/01/2020	666223		3,193.00
30/01/20​	000009	8,300.00			28/01/2020	921003		11,287.50
30/01/20​	000064	26,250.00			28/01/2020	984020		16,250.00
30/01/20​	000089	8,300.00			28/01/2020	000021		10,800.00
30/01/20​	805146	5,000.00			28/01/2020	000002	13,750.00
30/01/20​	000017	12,500.00			28/01/2020	SDM Dep		3,000.00
30/01/20​	000056	5,000.00			28/01/2020	SDM Dep		50.00
30/01/20​	000027	8,700.00			28/01/2020	000146		7,500.00
30/01/20​	000018	5,500.00			28/01/2020	000038		7,000.00
30/01/20​	302159	5,000.00			28/01/2020	000036		13,200.00
30/01/20​	000002	7,500.00			28/01/2020	000084		1,100.00
30/01/20​	000384	11,421.00			28/01/2020	000065		4,750.00
30/01/20​	000004	7,500.00			28/01/2020	000161		1,787.00
30/01/20​	000012	15,000.00			28/01/2020	000039		15,000.00
30/01/20​	000051	6,500.00			28/01/2020	000006		11,400.00
30/01/20​	000142B	200.00			28/01/2020	000081		10,000.00
30/01/20​	000142A	9,500.00			28/01/2020	000007		9,000.00
30/01/20​	002909	2,360.00			28/01/2020	001107		10,500.00
30/01/20​	000016B	200.00			28/01/2020	000037		13,750.00
30/01/20​	000016A	11,250.00			28/01/2020	000105		15,000.00
30/01/20​	000010	5,000.00			28/01/2020	000012		5,000.00
30/01/20​	100021	9,500.00			28/01/2020	000039		12,500.00
30/01/20​	000331	1,950.00			28/01/2020	000053		6,600.00
30/01/20​	000365B	200.00			28/01/2020	000014		10,750.00
30/01/20​	000365A	11,250.00			28/01/2020	000070		21,772.00
30/01/20​	000016	3,500.00			28/01/2020	000004		4,493.00
30/01/20​	100005	6,300.00			28/01/2020	000007		8,350.00
30/01/20​	119528B	200.00			28/01/2020	000005		21,250.00
30/01/20​	119528A	18,800.00			28/01/2020	000025		17,500.00
30/01/20​	076727	3,500.00			28/01/2020	Salary	307,326.00
30/01/20​	000014	8,750.00			29/01/2020	011180	350,000.00
30/01/20​	001752	21,000.00			29/01/2020	011256	111,755.70
30/01/20​	854243B	100.00			29/01/2020	011257	75,692.00
30/01/20​	000004	9,750.00			29/01/2020	011260	24,265.50
30/01/20​	000124	25,000.00			29/01/2020	CASH		102,280.25
30/01/20​	000310	3,600.00			29/01/2020	CASH		100,000.00
30/01/20​	000018B	200.00			29/01/2020	Charges	0.50
30/01/20​	000012B	200.00			29/01/2020	Charges	10.00
30/01/20​	000018A	15,250.00			29/01/2020	Charges	5.00
30/01/20​	000012A	15,250.00			29/01/2020	Charges	100.00
30/01/20​	000309	15,750.00			29/01/2020	011286	250,000.00
30/01/20​	000021	10,000.00			29/01/2020	000002		13,500.00
30/01/20​	548980B	200.00			29/01/2020	000003		6,000.00
30/01/20​	962150	9,000.00			29/01/2020	000004		5,500.00
30/01/20​	548980A	13,000.00			29/01/2020	000005		10,750.00
30/01/20​	008028	17,500.00			29/01/2020	000005		7,800.00
30/01/20​	000122	9,600.00			29/01/2020	000006		18,000.00
30/01/20​	000017	2,000.00			29/01/2020	000007		4,800.00
30/01/20​	000015	9,500.00			29/01/2020	000008		13,250.00
30/01/20​	165349	11,250.00			29/01/2020	000010		6,300.00
30/01/20​	500051	16,250.00			29/01/2020	000012		8,000.00
30/01/20​	000064	16,250.00			29/01/2020	000015		3,000.00
30/01/20​	000059	13,750.00			29/01/2020	000015		4,000.00
30/01/20​	000141	2,500.00			29/01/2020	000015		5,340.00
30/01/20​	200735	3,750.00			29/01/2020	000024		6,835.00
30/01/20​	000084	10,700.00			29/01/2020	000031		14,000.00
30/01/20​	487580	11,000.00			29/01/2020	000038		8,750.00
30/01/20​	200734	18,750.00			29/01/2020	000039		5,800.00
30/01/20​	000173	5,750.00			29/01/2020	000051		15,000.00
30/01/20​	100041B	800.00			29/01/2020	000081		24,075.00
30/01/20​	100041A	16,000.00			29/01/2020	000082		9,750.00
30/01/20​	000148	10,300.00			29/01/2020	000091		2,520.00
30/01/20​	017807	924,000.00			29/01/2020	000148		13,500.00
30/01/20​	000098B	200.00			29/01/2020	000217		5,500.00
30/01/20​	000098D	2,835.00			29/01/2020	000221		1,800.00
30/01/20​	000098C	2,700.00			29/01/2020	001281		14,000.00
30/01/20​	000010	200.00			29/01/2020	002979		6,600.00
30/01/20​	377212B	200.00			29/01/2020	004630		2,725.00
30/01/20​	000025B	2,600.00			29/01/2020	009292		10,833.00
30/01/20​	000025A	13,000.00			29/01/2020	011780		19,940.00
30/01/20​	377212A	5,000.00			29/01/2020	100005		4,150.00
30/01/20​	500026	11,500.00			29/01/2020	100006		4,500.00
30/01/20​	000098A	9,000.00			29/01/2020	100009		9,300.00
30/01/20​	100137	6,904.00			29/01/2020	100022		4,750.00
30/01/20​	000225	21,000.00			29/01/2020	287725		11,000.00
30/01/20​	854243A	5,400.00			29/01/2020	500056		7,500.00
30/01/20​	100016	10,200.00			29/01/2020	500059		9,330.00
30/01/20​	000002	24,187.50			29/01/2020	500190		7,700.00
30/01/20​	100047	30,000.00			29/01/2020	837261		14,200.00
30/01/20​	AL STLM	820.00			29/01/2020	983950		10,000.00
31/01/20​	TELLER FEE		10.50		29/01/2020	984919		20,000.00
31/01/20​	cb		33,201,554.85		29/01/2020	000045		7,000.00
		35,621,288.40	35,621,288.40		29/01/2020	000002	13,500.00
		-			29/01/2020	000006	18,000.00
					29/01/2020	000008	13,250.00
					29/01/2020	000012	8,000.00
					29/01/2020	000024	6,835.00
					29/01/2020	000045	7,000.00
					29/01/2020	000104		14,500.00
					29/01/2020	000038		5,500.00
					29/01/2020	000041		200.00
					29/01/2020	000142		12,500.00
					29/01/2020	007911		3,402.00
					29/01/2020	000153		15,750.00
					29/01/2020	TRF		500.00
					29/01/2020	000038	5,500.00
					29/01/2020	000041	200.00
					29/01/2020	Salary	286,057.00
					30/01/2020	011162	800,000.00
					30/01/2020	011167	800,000.00
					30/01/2020	011250	49,077.00
					30/01/2020	011275	109,425.75
					30/01/2020	BANK TRF		200.00
					30/01/2020	BANK TRF		15,700.00
					30/01/2020	BANK TRF		1,940.00
					30/01/2020	CASH		1,370.00
					30/01/2020	CASH		820.00
					30/01/2020	CASH		70,497.75
					30/01/2020	011287	42,236.60
					30/01/2020	000001		2,000.00
					30/01/2020	000002		3,500.00
					30/01/2020	000012		8,200.00
					30/01/2020	000022		200.00
					30/01/2020	000124		7,200.00
					30/01/2020	000132		13,500.00
					30/01/2020	000136		2,700.00
					30/01/2020	000137		200.00
					30/01/2020	000218		3,875.00
					30/01/2020	000247		17,100.00
					30/01/2020	000815		1,575.00
					30/01/2020	001560		32,400.00
					30/01/2020	001564		29,700.00
					30/01/2020	005724		21,250.00
					30/01/2020	100004		6,700.00
					30/01/2020	100022		10,000.00
					30/01/2020	100032		4,000.00
					30/01/2020	100060		8,400.00
					30/01/2020	215818		16,250.00
					30/01/2020	355949		7,550.00
					30/01/2020	100022	10,000.00
					31/01/2020	CB	34,912,487.60
					31/01/2020	Transfer	10.00
					31/01/2020	Sal Charges	0.50
							40,488,082.34	40,488,082.34
							-

 

[vinukk4]

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]

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
	A	B	C	D	E	F
1	Total of visible entries			882.50
2	References Unique	TRUE
3	Date	Description	Reference	Amount	Balance	Cleared
4	01-Mar-20		Dep2020.1	500.00	500.00	02-Mar-20
5	05-Mar-20		12	(15.00)	485.00	07-Mar-20
6	10-Mar-20		14	(100.00)	385.00	11-Mar-20
7	15-Mar-20		sc202003a	(2.50)	382.50	16-Mar-20
8	31-Mar-20		Dep2020.2	1,000.00	1,382.50	31-Mar-20
9	31-Mar-20		Dep2020.3	8,000.00	9,382.50	31-Mar-20
10	31-Mar-20		15	(5,000.00)	4,382.50
11	31-Mar-20		16	(3,500.00)	882.50
12
GL

Cell Formulas
Range	Formula
D1	D1	=SUBTOTAL(109,GL[Amount])
B2	B2	=SUMPRODUCT((C1:C5000<>"")/COUNTIF(C1:C5000,C1:C5000&""))=COUNTA(C:C)
E4:E11	E4	=SUM($D$4:D4)
F4:F11	F4	=SUMPRODUCT(--(BK[Reference]=[@Reference]),--(BK[Amount]=[@Amount]),BK[Date])

 

[Dave Patton]

Bank Rec 2020.xlsm
	A	B	C	D	E	F
1	Total of visible entries			9,377.50
2	References unique	TRUE
3	Date	Description	Reference	Amount	Balance	Cleared
4	02-Mar-20		Dep2020.1	500.00	500.00	TRUE
5	07-Mar-20		12	(15.00)	485.00	TRUE
6	11-Mar-20		14	(100.00)	385.00	TRUE
7	16-Mar-20		sc202003a	(2.50)	382.50	TRUE
8	31-Mar-20		sc202003b	(5.00)	377.50	FALSE
9	31-Mar-20		Dep2020.2	1,000.00	1,377.50	TRUE
10	31-Mar-20		Dep2020.3	8,000.00	9,377.50	TRUE
11
Bank

Cell Formulas
Range	Formula
D1	D1	=SUBTOTAL(109,BK[Amount])
B2	B2	=SUMPRODUCT((C1:C5000<>"")/COUNTIF(C1:C5000,C1:C5000&""))=COUNTA(C:C)
E4:E10	E4	=SUM($D$4:D4)
F4:F10	F4	=SUMPRODUCT(--(GL[Reference]=[@Reference]),--(GL[Amount]=[@Amount]))=1

Bank Rec 2020.xlsm
	B	C	D
1	Bank Reconciliation		31-Mar-20
2
3	Bank Statement - Balance	9,377.50
4	Outstanding entries	(8,500.00)
5	Unrecorded bank entries	5.00
6	Balance	882.50
7	GL - Balance	882.50
8	Diffference	0.00	J
9
10	D7 Has Wingdings font
11
Rec

Cell Formulas
Range	Formula
D1	D1	=LOOKUP(BigNum,(GL[Date]))
C3	C3	=SUM(BK[Amount])
C4	C4	=SUMIFS(GL[Amount],GL[Cleared],0)
C5	C5	=-SUMIFS(BK[Amount],BK[Cleared],FALSE)
C6	C6	=SUM(C3:C5)
C7	C7	=SUM(GL[Amount])
C8	C8	=C6-C7
D8	D8	=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])