search for (name, date amount) together based on sheets names and copy across sheets

[Ali M]

Hi
in DAILY sheet I have items contains sheet name in last part example (FROM ALI , FROM OMAR ,....)
and I have sheets names ALI and OMAR so I want matching in last PART in the item in column B with sheets names based on DATE & AMOUNT together even duplicates the same amount and date for the same name should brings . if I have the items in DAILY sheet for CUSTOMER NAME doesn't exist in CUSTOMER sheet based on matching date, amount and last part the item with sheet name then should copy to sheet is relation of it as I highlighted by red in DAILY sheet (those are not existed in ALI, OMAR sheet ) so should add to them and delete FROM ALI, FROM OMAR) , I don't need it to show in customers sheets .
if I have the items in CUSTOMERS sheets and don't existed in DAILY sheet as I highlighted by blue then should copy to DAILY sheet and add in last part of the ITEM "FROM ALI, FROM OMAR based on matching date, amount and last part the item with sheet name .
any item doesn't contain the sheet name in last part in DAILY sheet should ignore it and any word "OPENING BALANCE" also should ignores from customers sheets when matching.
every time I add new sheets and change data in all of sheets so should update every time run the macro.
also posted here
search for (name, date amount) together based on sheets names and copy across sheets


original data in sheets

copy (3) (1).xlsm
	A	B	C	D	E
1	DATE	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	20/06/2023	PURCHASE FROM OMAR	15,000.00		15,000.00
3	21/06/2023	CASH PR FROM ALI	15,000.00		30,000.00
4	21/06/2023	STOCK1	100,000.00		130,000.00
5	22/06/2023	CASH DM FROM ALI		37,000.00	93,000.00
6	22/06/2023	EXPENSE ADMIN		65,000.00	28,000.00
7	22/06/2023	PURCHASE RETURNS FROM OMAR		14,000.00	14,000.00
8	22/06/2023	PURCHASE RETURNS FROM ALI		3,200.00	10,800.00
9	22/06/2023	PURCHASE RETURNS FROM ALI		3,200.00	7,600.00
10	22/06/2023	PURCHASE LOW	6,200.00		13,800.00
11	22/06/2023	EXPENSE PR	37,000.00		50,800.00
12	22/06/2023	SALES		201,000.00	-150,200.00
13	22/06/2023	SALES RETURNS	3,500.00		-146,700.00
14	23/06/2023	SELLING LOW		3,201.00	-149,901.00
15	24/06/2023	SALES FROM OMAR		3,202.00	-153,103.00
DAILY

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3:E15	E3	=E2+C3-D3

copy (3) (1).xlsm
	A	B	C	D	E
1	ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	20/06/2023	OPENING BALANCE SALES	15,000.00		15,000.00
3	22/06/2023	PURCHASE RETURNS		4,000.00	11,000.00
4	24/06/2023	SALES		3,202.00	7,798.00
5		TOTAL	15,000.00	7,202.00	7,798.00
OMAR

Cell Formulas
Range	Formula
E2,E5	E2	=C2-D2
E3:E4	E3	=E2+C3-D3
C5:D5	C5	=SUM(C2:C4)

copy (3) (1).xlsm
	A	B	C	D	E
1	DATE	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	20/06/2023	OPENING BALANCE PURCHASE	200,000.00		200,000.00
3	21/06/2023	CASH PR	15,000.00		215,000.00
4	22/06/2023	CASH DM		30,000.00	185,000.00
5	23/06/2023	CASH DM		1,200.00	183,800.00
6	23/06/2023	CASH DM		1,200.00	182,600.00
7		TOTAL	215,000.00	32,400.00	182,600.00
ALI

Cell Formulas
Range	Formula
E2,E7	E2	=C2-D2
E3:E6	E3	=E2+C3-D3
C7:D7	C7	=SUM(C2:C6)

result in each sheet

copy (3) (1).xlsm
	A	B	C	D	E
1	DATE	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	21/06/2023	CASH PR FROM ALI	15,000.00		15,000.00
3	21/06/2023	CASH PR FROM ALI	15,000.00		30,000.00
4	22/06/2023	CASH DM FROM ALI		37,000.00	-7,000.00
5	22/06/2023	EXPENSE ADMIN		65,000.00	-72,000.00
6	22/06/2023	CASH DM FROM ALI		30,000.00	-102,000.00
7	23/06/2023	CASH DM FROM ALI		1,200.00	-103,200.00
8	23/06/2023	CASH DM FROM ALI		1,200.00	-104,400.00
9	20/06/2023	PURCHASE FROM OMAR	15,000.00		-89,400.00
10	22/06/2023	PURCHASE RETURNS FROM OMAR		4,000.00	-93,400.00
11	21/06/2023	STOCK1	100,000.00		10,600.00
12	22/06/2023	PURCHASE RETURNS FROM OMAR		14,000.00	-3,400.00
13	22/06/2023	PURCHASE LOW	6,200.00		2,800.00
14	22/06/2023	EXPENSE PR	37,000.00		39,800.00
15	22/06/2023	SALES		201,000.00	-161,200.00
16	22/06/2023	SALES RETURNS	3,500.00		-157,700.00
17	22/06/2023	PURCHASE RETURNS FROM ALI		3,200.00	-160,900.00
18	22/06/2023	PURCHASE RETURNS FROM ALI		3,200.00	-164,100.00
19	23/06/2023	SELLING LOW		3,201.00	-167,301.00
20	24/06/2023	SALES FROM OMAR		3,202.00	-170,503.00
DAILY

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E12:E20,E3:E10	E3	=E2+C3-D3
E11	E11	=E9+C11-D11

copy (3) (1).xlsm
	A	B	C	D	E
1	ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	20/06/2023	OPENING BALANCE SALES	15,000.00		15,000.00
3	20/06/2023	PURCHASE	15,000.00		30,000.00
4	22/06/2023	PURCHASE RETURNS		14,000.00	16,000.00
5	22/06/2023	PURCHASE RETURNS		4,000.00	12,000.00
6	24/06/2023	SALES		3,202.00	8,798.00
7		TOTAL	30,000.00	21,202.00	8,798.00
OMAR

Cell Formulas
Range	Formula
E2,E7	E2	=C2-D2
E3:E6	E3	=E2+C3-D3
C7:D7	C7	=SUM(C2:C6)

copy (3) (1).xlsm
	A	B	C	D	E
1	DATE	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	20/06/2023	OPENING BALANCE PURCHASE	200,000.00		200,000.00
3	21/06/2023	CASH PR	15,000.00		215,000.00
4	21/06/2023	CASH PR	15,000.00		230,000.00
5	22/06/2023	CASH DM		37,000.00	193,000.00
6	22/06/2023	PURCHASE RETURNS		3,200.00	189,800.00
7	22/06/2023	PURCHASE RETURNS		3,200.00	186,600.00
8	22/06/2023	CASH DM		30,000.00	156,600.00
9	23/06/2023	CASH DM		1,200.00	155,400.00
10	23/06/2023	CASH DM		1,200.00	154,200.00
11		TOTAL	230,000.00	75,800.00	154,200.00
ALI

Cell Formulas
Range	Formula
E2,E11	E2	=C2-D2
E3:E10	E3	=E2+C3-D3
C11:D11	C11	=SUM(C2:C10)

thanks