Create report by matching part of item and populate for each cell under item across sheets

[Ali M]

Hello
in SAFE sheet will match header(whole item) from column B to last column with column D across sheet and match part of item in column A from row 2 with column E across sheet after that should merge amount is existed in TOTAL row for last column (TOTAL) for each sheet contains items in column D,E when match with header and column A for SAFE sheet and should calculate as highlighted cells contain formulas . last thing should fill empty cell zero as hyphen .
I would macro to deal with big data for each sheet

ITEMS (1).xlsm
	A	B	C	D	E	F
1	ITEM	DATE	ID	INVOICES TYPE	INVOICE NO	TOTAL
2	1	01/01/2023	DFRO200	CASH INVOICES	PURCHASE INVOICE NO AD3333	13,000.00
3	2	01/01/2023	DFRO201	CASH INVOICES	PURCHASE INVOICE NO AD3333	4,000.00
4	3	01/01/2023	DFRO202	CASH INVOICES	PURCHASE INVOICE NO AD3333	3,000.00
5	TOTAL					20,000.00
6	1	01/01/2023	DFRO203	CASH INVOICES	PURCHASE INVOICE NO AD3334	8,000.00
7	2	01/01/2023	DFRO204	CASH INVOICES	PURCHASE INVOICE NO AD3334	4,000.00
8	TOTAL					12,000.00
9	1	01/01/2023	DFRO203	CASH INVOICES	PURCHASE INVOICE NO AD3335	8,000.00
10	2	02/01/2023	DFRO204	CASH INVOICES	PURCHASE INVOICE NO AD3335	12,000.00
11	3	03/01/2023	DFRO205	CASH INVOICES	PURCHASE INVOICE NO AD3335	100,000.00
12	4	04/01/2023	DFRO206	CASH INVOICES	PURCHASE INVOICE NO AD3335	50,000.00
13	5	05/01/2023	DFRO207	CASH INVOICES	PURCHASE INVOICE NO AD3335	40,000.00
14	6	06/01/2023	DFRO208	CASH INVOICES	PURCHASE INVOICE NO AD3335	10,000.00
15	TOTAL					220,000.00
16	1	06/01/2023	DFRO203	CASH INVOICES	PURCHASE INVOICE NO AD3336	11,000.00
17	TOTAL					11,000.00
18	1	06/01/2023	DFRO208	FORWARD INVOICES	PURCHASE INVOICE NO AD3337	13,000.00
19	TOTAL					13,000.00
20	1	06/01/2023	DFRO207	FORWARD INVOICES	PURCHASE INVOICE NO AD3338	11,000.00
21	TOTAL					11,000.00
22	1	06/01/2023	DFRO206	FORWARD INVOICES	PURCHASE INVOICE NO AD3339	1,500.00
23	TOTAL					1,500.00
PURCHASE

ITEMS (1).xlsm
	A	B	C	D	E	F
1	ITEM	DATE	ID	INVOICES TYPE	INVOICE NO	TOTAL
2	1	02/01/2023	DFRO201	CASH INVOICES	SALES INVOICE NO SA1222222	6,000.00
3	2	02/01/2023	DFRO202	CASH INVOICES	SALES INVOICE NO SA1222222	6,000.00
4	TOTAL					12,000.00
5	1	02/01/2023	DFRO203	CASH INVOICES	SALES INVOICE NO SA1222223	12,000.00
6	2	02/01/2023	DFRO206	CASH INVOICES	SALES INVOICE NO SA1222223	60,000.00
7	3	02/01/2023	DFRO200	CASH INVOICES	SALES INVOICE NO SA1222223	28,000.00
8	TOTAL					110,000.00
9	1	02/01/2023	DFRO203	FORWARD INVOICES	SALES INVOICE NO SA1222224	12,000.00
10	TOTAL					12,000.00
11	1	02/01/2023	DFRO201	FORWARD INVOICES	SALES INVOICE NO SA1222225	6,000.00
12	2	02/01/2023	DFRO202	FORWARD INVOICES	SALES INVOICE NO SA1222225	6,000.00
13	TOTAL					12,000.00
14	1	04/01/2023	DFRO202	CASH INVOICES	SALES INVOICE NO SA1222226	4,000.00
15	TOTAL					4,000.00
SALES

ITEMS (1).xlsm
	A	B	C	D	E	F
1	ITEM	DATE	ID	INVOICES TYPE	INVOICE NO	TOTAL
2	1	02/01/2023	DFRO201	FORWARD INVOICES	SALES RETURNS INVOICE NO ST34444	1,000.00
3	2	02/01/2023	DFRO202	FORWARD INVOICES	SALES RETURNS INVOICE NO ST34444	200.00
4	TOTAL					1,200.00
5	1	03/01/2023	DFRO202	CASH INVOICES	SALES RETURNS INVOICE NO ST34445	2,000.00
6	TOTAL					2,000.00
7	1	03/01/2023	DFRO201	CASH INVOICES	SALES RETURNS INVOICE NO ST34446	3,000.00
8	TOTAL					3,000.00
9	1	03/01/2023	DFRO202	FORWARD INVOICES	SALES RETURNS INVOICE NO ST34447	1,400.00
10	TOTAL					1,400.00
11	1	04/01/2023	DFRO201	CASH INVOICES	SALES INVOICE NO SA1222226	4,000.00
12	TOTAL					4,000.00
SALES RETURNS

ITEMS (1).xlsm
	A	B	C	D	E	F
1	ITEM	DATE	ID	INVOICES TYPE	INVOICE NO	TOTAL
2	1	03/01/2023	DFRO202	FORWARD INVOICES	PURCHASE RETURNS INVOICE NO PR 1222	2,200.00
3	TOTAL					2,200.00
4	1	03/01/2023	DFRO201	CASH INVOICES	PURCHASE RETURNS INVOICE NO PR 1223	1,300.00
5	TOTAL					1,300.00
6	1	03/01/2023	DFRO201	CASH INVOICES	PURCHASE RETURNS INVOICE NO PR 1224	1,400.00
7	TOTAL					1,400.00
PURCHASE RETURNS

ITEMS (1).xlsm
	A	B	C	D	E
1	ITEM	DATE	VOUCHER TYPE	VOUCHER NO	TOTAL
2	1	01/02/2023	CASH IN	CAH IN VOUCHER NO 12222	3,000.00
3	2	01/02/2023	CASH IN	CAH IN VOUCHER NO 12223	12,000.00
4	3	01/02/2023	CASH OUT	CAH OUT VOUCHER NO 44444	1,000.00
5	4	01/02/2023	CASH IN	CAH IN VOUCHER NO 12224	3,000.00
6	5	02/02/2023	CASH OUT	CAH OUT VOUCHER NO 44445	5,000.00
7	6	02/02/2023	CASH OUT	CAH OUT VOUCHER NO 44446	5,000.00
8	7	02/02/2023	CASH OUT	CAH OUT VOUCHER NO 44447	2,500.00
9	8	02/02/2023	CASH IN	CAH IN VOUCHER NO 12225	3,000.00
10	9	02/02/2023	CASH IN	CAH IN VOUCHER NO 12226	2,500.00
VOUCHER

ITEMS (1).xlsm
	A	B	C	D	E
1	ITEM	DATE	TYPE	DETAILES	TOTAL
2	1	01/02/2023	CASH EXPENSES	SERVICES EXPENSES	5,000.00
3	2	01/02/2023	CASH EXPENSES	FOOD EXPENSES	1,200.00
4	3	01/02/2023	CASH EXPENSES	TRANSPORTATION EXPENSES	3,000.00
5	4	02/02/2023	CASH EXPENSES	LABOR EXPENSES	3,000.00
6	5	03/02/2023	CASH EXPENSES	HIRE EXPENSES	15,000.00
7	6	04/02/2023	CASH SALARY	SALARY EXPENSES	15,000.00
8	7	05/02/2023	CASH IN EMPLOYEE	CASH IN FROM EMPLOYEE	2,000.00
9	8	06/02/2023	CASH OUT EMPLOYEE	CASH OUT TO EMPLOYEE	3,000.00
10	9	07/02/2023	CASH IN EMPLOYEE	CASH IN FROM EMPLOYEE	2,500.00
11	10	08/03/2023	CASH SALARY	SALARY EXPENSES	15,000.00
12	11	09/03/2023	ADVANCE PAYMENT OF SALARY	ADVANCE PAYMENT EXPENSES	4,000.00
13	12	10/03/2023	ADVANCE PAYMENT OF SALARY	ADVANCE PAYMENT EXPENSES	1,200.00
14	13	11/03/2023	CASH IN SAFE	CASH IN FROM REVENUE	3,000.00
15	14	12/03/2023	CASH OUT SAFE	CASH OUT TO CUSTOMER	5,500.00
16	15	13/03/2023	CASH IN SAFE	CASH IN FROM REVENUE	10,000.00
17	16	14/03/2023	CASH OUT SAFE	CASH OUT TO CUSTOMER	1,200.00
18	17	15/03/2023	EXTERNAL CASH DEPOSIT IN SAFE	CASH IN SAFE VN10000	3,000.00
19	18	16/03/2023	EXTERNAL CASH WITHDRAWAL FROM SAFE	CASH OUT SAFE N10001	5,000.00
20	19	17/03/2023	EXTERNAL CASH DEPOSIT IN SAFE	CASH IN SAFE VN10001	7,000.00
21	20	18/03/2023	EXTERNAL CASH WITHDRAWAL FROM SAFE	CASH OUT SAFE N10002	9,000.00
22	21	19/03/2023	SURPLUSE ADJUSTMENT	CASH IN SAFE VN10002	220.00
23	22	20/03/2023	SURPLUSE ADJUSTMENT	CASH IN SAFE VN10003	200.00
24	23	21/03/2023	SURPLUSE ADJUSTMENT	CASH IN SAFE VN10004	120.00
25	24	22/03/2023	DEFICIT ADJUSTMENT	CASH OUT SAFE N10003	200.00
26	25	23/03/2023	DEFICIT ADJUSTMENT	CASH OUT SAFE N10004	300.00
27	26	24/03/2023	CASH EXPENSES	OTHERS EXPENSES VB12222	1,200.00
28	27	25/03/2023	CASH EXPENSES	OTHERS EXPENSES VB12223	1,230.00
29	28	26/03/2023	EXTERNAL BANK DEPOSIT	CASH IN BANK VN10004	2,000.00
30	29	27/03/2023	EXTERNAL CASH WITHDRAWAL	CASH OUT OUT N100034	1,000.00
31	30	28/03/2023	EXTERNAL BANK DEPOSIT	CASH IN BANK VN1000114	2,000.00
32	31	29/03/2023	EXTERNAL CASH WITHDRAWAL	CASH OUT BANK N100013	1,000.00
CASH

before

ITEMS V3.xlsm
	A	B	C	D	E	F	G
1	MOVEMENT	CASH INVOICES	FORWARD INVOICES	INVOICES TOTAL	CASH IN	CASH OUT	EXPENSES
2	PURCHASE
3	PURCHASE RETURNS
4	NET PURCHASE
5	SALES
6	SALES RETURNS
7	NET SALES
8	CASH IN
9	CASH IN SAFE
10	CASH OUT
11	CASH EXPENSES
12	CASH SALARY
13	CASH OUT SAFE
14	SURPLUSE ADJUSTMENT
15	DEFICIT ADJUSTMENT
16	ADVANCE PAYMENT OF SALARY
17	CASH IN EMPLOYEE
18	CASH OUT EMPLOYEE
19	TOTAL EXPENSES
20	EXTERNAL CASH DEPOSIT IN SAFE
21	EXTERNAL CASH WITHDRAWAL FROM SAFE
22	NET SAFE
23	EXTERNAL BANK DEPOSIT
24	EXTERNAL CASH WITHDRAWAL
25	NET BANK
SAFE

result should be

ITEMS (1).xlsm
	A	B	C	D	E	F	G
1	MOVEMENT	CASH INVOICES	FORWARD INVOICES	INVOICES TOTAL	CASH IN	CASH OUT	EXPENSES
2	PURCHASE	263,000.00	25,500.00	288,500.00	-	-	-
3	PURCHASE RETURNS	2,700.00	2,200.00	4,900.00	-	-	-
4	NET PURCHASE	-	-	283,600.00	-	-	-
5	SALES	126,000.00	24,000.00	150,000.00	-	-	-
6	SALES RETURNS	6,233.00	2,600.00	8,833.00	-	-	-
7	NET SALES	-	-	141,167.00	-	-	-
8	CASH IN	-	-	-	23,500.00	-	-
9	CASH IN SAFE	-	-	-	13,000.00	-	-
10	CASH OUT	-	-	-	-	13,500.00	-
11	CASH EXPENSES	-	-	-	-	-	29,630.00
12	CASH SALARY	-	-	-	-	-	30,000.00
13	CASH OUT SAFE	-	-	-	-	6,700.00	-
14	SURPLUSE ADJUSTMENT	-	-	-	540.00	-	-
15	DEFICIT ADJUSTMENT	-	-	-	-	500.00	-
16	ADVANCE PAYMENT OF SALARY	-	-	-	-	-	5,200.00
17	CASH IN EMPLOYEE	-	-	-	4,500.00	-	-
18	CASH OUT EMPLOYEE	-	-	-	-	3,000.00	-
19	TOTAL EXPENSES	64,830.00	-	-	-	-	-
20	EXTERNAL CASH DEPOSIT IN SAFE	-	-	-	10,000.00	-	-
21	EXTERNAL CASH WITHDRAWAL FROM SAFE	-	-	-	-	14,000.00	-
22	NET SAFE	15,840.00	-	-	-	-	-
23	EXTERNAL BANK DEPOSIT	-	-	-	4,000.00	-	-
24	EXTERNAL CASH WITHDRAWAL	-	-	-	-	2,000.00	-
25	NET BANK	2,000.00	-	-	-	-	-
SAFE

the calculation in some cells should be as highlighted cell

ITEMS (1).xlsm
	A	B	C	D	E	F	G
1	MOVEMENT	CASH INVOICES	FORWARD INVOICES	INVOICES TOTAL	CASH IN	CASH OUT	EXPENSES
2	PURCHASE	263,000.00	25,500.00	288,500.00	-	-	-
3	PURCHASE RETURNS	2,700.00	2,200.00	4,900.00	-	-	-
4	NET PURCHASE	-	-	283,600.00	-	-	-
5	SALES	126,000.00	24,000.00	150,000.00	-	-	-
6	SALES RETURNS	6,233.00	2,600.00	8,833.00	-	-	-
7	NET SALES	-	-	141,167.00	-	-	-
8	CASH IN	-	-	-	23,500.00	-	-
9	CASH IN SAFE	-	-	-	13,000.00	-	-
10	CASH OUT	-	-	-	-	13,500.00	-
11	CASH EXPENSES	-	-	-	-	-	29,630.00
12	CASH SALARY	-	-	-	-	-	30,000.00
13	CASH OUT SAFE	-	-	-	-	6,700.00	-
14	SURPLUSE ADJUSTMENT	-	-	-	540.00	-	-
15	DEFICIT ADJUSTMENT	-	-	-	-	500.00	-
16	ADVANCE PAYMENT OF SALARY	-	-	-	-	-	5,200.00
17	CASH IN EMPLOYEE	-	-	-	4,500.00	-	-
18	CASH OUT EMPLOYEE	-	-	-	-	3,000.00	-
19	TOTAL EXPENSES	64,830.00	-	-	-	-	-
20	EXTERNAL CASH DEPOSIT IN SAFE	-	-	-	10,000.00	-	-
21	EXTERNAL CASH WITHDRAWAL FROM SAFE	-	-	-	-	14,000.00	-
22	NET SAFE	15,840.00	-	-	-	-	-
23	EXTERNAL BANK DEPOSIT	-	-	-	4,000.00	-	-
24	EXTERNAL CASH WITHDRAWAL	-	-	-	-	2,000.00	-
25	NET BANK	2,000.00	-	-	-	-	-
SAFE

Cell Formulas
Range	Formula
D5:D6,D2:D3	D2	=B2+C2
D4,D7	D4	=D2-D3
B19	B19	=G11+G12+G16
B22	B22	=(E8+E9+E14+E17+E20+E23)-(F10+F13+F15+F18+F21+F24)
B25	B25	=E23-F24

here is sample simple file to see close, hope there is no error in my details , if it's so please inform me to correcting.
Dropbox

 

[Ali M]

one week no answering so far !

 

[Ali M]