macro to match partial item in list across sheet and populate amounts.

[Abdo]

Hi experts,
maybe this way see some members is strange or not logic about calculation , but I need this way .
my idea will match partial item in list in SUMMARY sheet with the same partial item across sheets , if the same partial of the item is existed in columns DEBIT , CREDIT . first of all will merge amount for DEBIT or CREDIT column in the same sheet , second should sum DEBIT,CREDIT columns together
example : in summary sheet will match PUR PURCHASE across sheets .
in CSMN PURCHASE will be after sum DEBIT=112,800 and in CSMT PURCHASE sheet will be after sum CREDIT =66000 so the final result as show in summary sheet will be = 112800+66000=178800 and so on for the others items also I will add new sheets before summary sheet with the same structure and the same idea .
every time I will add new items in column B for summary sheet .

REF.xlsm
	A	B	C	D
1	DATE	ACCOUNT REF	DEBIT	CREDIT
2	01/05/2023	PUR PURCHASE INV 000011	20,000.00
3	02/05/2023	PUR PURCHASE INV 000012	20,000.00
4	03/05/2023	PUR PURCHASE INV 000013	20,000.00
5	04/05/2023	PUR PURCHASE INV 000014	20,000.00
6	05/05/2023	PUR PURCHASE INV 000015	20,000.00
7	06/05/2023	PA PAYING 789		1,000.00
8	07/05/2023	PA PAYING 790		1,000.00
9	08/05/2023	PUR PURCHASE INV 000016	2,000.00
10	09/05/2023	PUR PURCHASE INV 000017	1,200.00
11	10/05/2023	PUR PURCHASE INV 000018	1,300.00
12	11/05/2023	PUR PURCHASE INV 000019	1,400.00
13	12/05/2023	PUR PURCHASE INV 000020	1,000.00
14	13/05/2023	PUR PURCHASE INV 000021	1,200.00
15	14/05/2023	PUR PURCHASE INV 000022	1,200.00
16	15/05/2023	PUR PURCHASE INV 000023	1,200.00
17	16/05/2023	PUR PURCHASE INV 000024	1,300.00
18	17/05/2023	PUR PURCHASE INV 000025	1,000.00
CSMN PURCHASE

REF.xlsm
	A	B	C	D
1	DATE	ACCOUNT REF	DEBIT	CREDIT
2	01/05/2023	PUR PURCHASE INV 0000111		2,000.00
3	02/05/2023	PUR PURCHASE INV 0000112		3,000.00
4	03/05/2023	PUR PURCHASE INV 0000113		4,000.00
5	04/05/2023	PUR PURCHASE INV 0000114		5,000.00
6	05/05/2023	PUR PURCHASE INV 0000115		6,000.00
7	06/05/2023	PUR PURCHASE INV 0000116		7,000.00
8	07/05/2023	PUR PURCHASE INV 0000117		8,000.00
9	08/05/2023	PUR PURCHASE INV 0000118		9,000.00
10	09/05/2023	PA PAYING 791	300.00
11	10/05/2023	PA PAYING 792	400.00
12	11/05/2023	PA PAYING 793	500.00
13	12/05/2023	PA PAYING 794	340.00
14	13/05/2023	PUR PURCHASE INV 0000119		3,000.00
15	14/05/2023	PUR PURCHASE INV 0000120		4,000.00
16	15/05/2023	PUR PURCHASE INV 0000121		5,000.00
17	16/05/2023	PUR PURCHASE INV 0000122		6,000.00
18	17/05/2023	PUR PURCHASE INV 0000123		4,000.00
CSMT PURCHASE

REF.xlsm
	A	B	C	D
1	DATE	ACCOUNT REF	DEBIT	CREDIT
2	01/05/2023	VC VOUCHER PAY 10001		1,200.00
3	02/05/2023	VC VOUCHER PAY 10002		1,800.00
4	03/05/2023	VC VOUCHER PAY 10003		1,200.00
5	04/05/2023	VC VOUCHER PAY 10004		1,300.00
6	05/05/2023	VC VOUCHER PAY 10005		1,400.00
7	06/05/2023	VC VOUCHER PAY 10006		1,500.00
8	07/05/2023	VC VOUCHER PAY 10007		1,700.00
9	08/05/2023	VC VOUCHER PAY 10008
10	09/05/2023	VC VOUCHER RECEIVED 10009	2,000.00
11	10/05/2023	VC VOUCHER RECEIVED 10010	3,000.00
12	11/05/2023	VC VOUCHER PAY 10011		600.00
13	12/05/2023	VC VOUCHER PAY 10012		700.00
14	13/05/2023	VC VOUCHER PAY 10013		600.00
CSMN CASH

REF.xlsm
	A	B	C	D
1	DATE	ACCOUNT REF	DEBIT	CREDIT
2	01/06/2023	VC VOUCHER RECEIVED V 1000		2,000.00
3	02/06/2023	VC VOUCHER RECEIVED V 1001		3,000.00
4	03/06/2023	VC VOUCHER RECEIVED V 1002		4,000.00
5	04/06/2023	VC VOUCHER RECEIVED V 1003		3,000.00
6	05/06/2023	VC VOUCHER PAY 10014	2,000.00
7	06/06/2023	VC VOUCHER PAY 10015	3,000.00
8	07/06/2023	VC VOUCHER PAY 10016	1,200.00
9	08/06/2023	VC VOUCHER PAY 10017	1,000.00
10	09/06/2023	VC VOUCHER PAY 10018	1,200.00
11	10/06/2023	VC VOUCHER PAY 10019	1,300.00
12	11/06/2023	VC VOUCHER RECEIVED V 1004		200.00
13	12/06/2023	VC VOUCHER RECEIVED V 1005		225.00
14	13/06/2023	VC VOUCHER RECEIVED V 1006		300.00
15	14/06/2023	VC VOUCHER RECEIVED V 1007		400.00
16	15/06/2023	VC VOUCHER RECEIVED V 1008		500.00
17	16/06/2023	VC VOUCHER RECEIVED V 1009		600.00
18	17/06/2023	VC VOUCHER RECEIVED V 1010		700.00
SRTRYUH CASH

REF.xlsm
	A	B	C	D
1	DATE	ACCOUNT REF	DEBIT	CREDIT
2	01/06/2023	SAL SALES SR0001		4,000.00
3	02/06/2023	SAL SALES SR0002		8,000.00
4	03/06/2023	SAL SALES SR0003		3,000.00
5	04/06/2023	SAL SALES SR0004		2,000.00
6	05/06/2023	SAL SALES SR0005		1,000.00
7	06/06/2023	SAL SALES SR0006		1,200.00
8	07/06/2023	SAL SALES SR0007		5,000.00
9	08/06/2023	RE SALES SR0008	2,000.00
10	09/06/2023	RE SALES SR0009	1,200.00
11	10/06/2023	RE SALES SR0010	1,300.00
12	11/06/2023	SAL SALES SR00011		4,000.00
13	12/06/2023	SAL SALES SR00012		1,200.00
14	13/06/2023	SAL SALES SR00013		2,000.00
15	14/06/2023	SAL SALES SR00014		2,000.00
16	15/06/2023	SAL SALES SR00015		2,000.00
17	16/06/2023	SAL SALES SR00016		2,000.00
18	17/06/2023	SAL SALES SR00017		2,000.00
SRTRYUH SR SALE

REF.xlsm
	A	B	C
1	ITEM	ACCOUNT REF	BALANCE
2	1	PUR PURCHASE
3	2	PA PAYING
4	3	VC VOUCHER PAY
5	4	VC VOUCHER RECEIVED
6	5	SAL SALES
7	6	RE SALES
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	CALCULATION
summary

OUTPUT should be

REF.xlsm
	A	B	C
1	ITEM	ACCOUNT REF	BALANCE
2	1	PUR PURCHASE	178,800.00
3	2	PA PAYING	3,540.00
4	3	VC VOUCHER PAY	21,700.00
5	4	VC VOUCHER RECEIVED	19,925.00
6	5	SAL SALES	39,400.00
7	6	RE SALES	4,500.00
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	CALCULATION
summary

thanks

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.