Merge partial character before specific word across sheets based on headers

[Amer Omar]

Hi,
I want merge data based on matching headers across sheets .
if the cells B4,C4 are empty then should match header(DETAILES) and merge amounts for column TOTAL based on partial character before word "NO"
if the cells B4,C4 are DATES then should match headers(DATE,DETAILES) and merge amounts for column TOTAL based on before word "NO"
as you see the structure for each sheet is not the same . so that's why I would match headers.
so the result will be in RESULT sheet .
in row 7 will be headers from column C based on sheets names and will merge based on two cases as I mentioned also insert total row to sum each column .
I would macro to deal with big data for each sheet , I will add new sheets every time before RESULT sheet . so before bring report should delete data in RESULT sheet.

AM (1).xlsm
	A	B	C	D	E
1	ITEM	DATE	DETAILES	REF	TOTAL
2	1	11/11/2023	SALES INVOICE NO 123333	REE1	2,000.00
3	2	11/11/2023	SALES INVOICE NO 123333	REE2	3,000.00
4	3	11/11/2023	SALES INVOICE NO 123333	REE2	2,500.00
5	4	11/11/2023	SALES INVOICE NO 123333	REE2	1,200.00
6	5	11/11/2023	SALES INVOICE NO 123333	REE3	1,300.00
7	6	11/11/2023	SALES INVOICE NO 123333	REE1	210.00
8	7	11/11/2023	SALES INVOICE NO 123333	REE1	1,310.00
9	8	11/11/2023	SALES INVOICE NO 123333	REE1	1,220.00
10	9	11/11/2023	SALES INVOICE NO 123333	REE3	1,110.00
11	TOTAL				13,850.00
12	1	11/11/2023	SALES INVOICE NO 123334	REE3	2,000.00
13	2	11/11/2023	SALES INVOICE NO 123334	REE4	3,000.00
14	3	11/11/2023	SALES INVOICE NO 123334	REE5	2,500.00
15	TOTAL				7,500.00
16	1	11/11/02024	SALES INVOICE NO 123334	REE3	2,000.00
17	2	11/11/02024	SALES INVOICE NO 123334	REE4	3,000.00
18	TOTAL				5,000.00
19	1	11/11/02024	SALES INVOICE NO 123334	REE3	4,000.00
20	2	11/11/02024	SALES INVOICE NO 123334	REE6	5,000.00
21	TOTAL				9,000.00
SAELS

Cell Formulas
Range	Formula
E11	E11	=SUM(E2:E10)
E15	E15	=SUM(E12:E14)
E18,E21	E18	=SUM(E16:E17)

AM (1).xlsm
	B	C	D	E	F
1	ITEM	DATE	DETAILES	REF	TOTAL
2	1	11/11/2023	PURCHASE INVOICE NO PR199000	PPRT1	1,000.00
3	2	11/11/2023	PURCHASE INVOICE NO PR199000	PPRT2	12,000.00
4	3	11/11/2023	PURCHASE INVOICE NO PR199000	PPRT3	20,000.00
5	4	11/11/2023	PURCHASE INVOICE NO PR199000	PPRT4	30,000.00
6	5	11/11/2023	PURCHASE INVOICE NO PR199000	PPRT5	4,000.00
7	6	11/11/2023	PURCHASE INVOICE NO PR199000	PPRT6	50,000.00
8	7	11/11/2023	PURCHASE INVOICE NO PR199000	PPRT7	1,000.00
9	TOTAL				118,000.00
10	1	11/01/2024	PURCHASE INVOICE NO PR199001	PPRT7	1,200.00
11	2	11/01/2024	PURCHASE INVOICE NO PR199001	PPRT8	1,300.00
12	TOTAL				2,500.00
13	1	11/01/2024	PURCHASE INVOICE NO PR199002	REE3	2,000.00
14	2	11/01/2024	PURCHASE INVOICE NO PR199002	REE4	3,000.00
15	TOTAL				5,000.00
PURCHASES

Cell Formulas
Range	Formula
F9	F9	=SUM(F2:F8)
F12,F15	F12	=SUM(F10:F11)

AM (1).xlsm
	C	D	E	F	G	H
1	ITEM	DATE	DETAILS	DEBIT	CREDIT	TOTAL
2	1	01/01/2023	PUFF PASW NO MM001	1,100.00		1,100.00
3	2	02/01/2023	PUFF PASW NO MM002	1,000.00		1,000.00
4	3	03/01/2023	MUFF SASW NO MM003	2,000.00	1,100.00	900.00
5	4	04/01/2023	PUFF PASW NO MM004	1,200.00		1,200.00
6	5	05/01/2023	ZUFF ZASW NO MM005	1,200.00		1,200.00
7	6	06/01/2023	PUFF PASW NO MM006	2,000.00	1,000.00	1,000.00
8	7	07/01/2024	PUFF PASW NO MM007	2,100.00	1,001.00	1,099.00
9	8	08/01/2024	PUFF PASW NO MM008	2,120.00	1,002.00	1,118.00
10	9	09/01/2024	PUFF PASW NO MM009	1,000.00	200.00	800.00
PDD

Cell Formulas
Range	Formula
H2:H10	H2	=F2-G2

AM (1).xlsm
	C	D	E	F	G	H
1	ITEM	DATE	DETAILS	DEBIT	CREDIT	TOTAL
2	1	01/01/2023	RCC VCH NO VM12222	2,000.00		2,000.00
3	2	02/01/2023	RCC VCH NO VM12223	3,000.00		3,000.00
4	3	03/01/2023	RCC VCH NO VM12224	2,100.00		2,100.00
5	4	04/01/2023	RCC VCH NO VM12225	1,200.00		1,200.00
6	5	05/01/2023	RCC VCH NO VM12226	1,200.00		1,200.00
7	6	06/01/2023	VRTC VCH NO 2000	2,000.00	1,000.00	1,000.00
8	7	07/01/2023	RCC VCH NO VM12227	2,200.00		2,200.00
9	8	08/01/2024	VRTC VCH NO 2001	2,000.00	1,500.00	500.00
10	9	08/01/2024	RCC VCH NO VM12228	2,000.00		2,000.00
11	10	08/01/2024	VRTC VCH NO 2002	1,200.00	1,100.00	100.00
12	11	08/01/2024	VRTC VCH NO 2003	1,300.00	1,200.00	100.00
PAID

Cell Formulas
Range	Formula
H2:H12	H2	=F2-G2

before

AM (1).xlsm
	A	B	C	D	E	F
3		FROM DATE	TO DATE
4
5
6
7
8
9
10
11
12
13
14
15
RESULT

when B4,C4 are empty

AM (1).xlsm
	A	B	C	D	E	F
3		FROM DATE	TO DATE
4
5
6
7	ITEM	DESCRIBE	SAELS	PURCHASES	PAID	PDD
8	1	PURCHASE INVOICE	-	125,500.00	-	-
9	2	SALES INVOICE	35,350.00	-	-	-
10	3	RCC VCH	-	-	13,700.00	-
11	4	VRTC VCH	-	-	1,700.00	-
12	5	PUFF PASW	-	-	-	7,137.00
13	6	MUFF SASW	-	-	-	900.00
14	7	ZUFF ZASW	-	-	-	1,200.00
15	TOTAL		35,350.00	125,500.00	15,400.00	9,237.00
RESULT

Cell Formulas
Range	Formula
C15:F15	C15	=SUM(C8:C14)

when B4,C4 contains dates

AM (1).xlsm
	A	B	C	D	E	F
3		FROM DATE	TO DATE
4		01/01/2023	12/11/2023
5
6
7	ITEM	DESCRIBE	SAELS	PURCHASES	PAID	PDD
8	1	PURCHASE INVOICE	-	118,000.00	-	-
9	2	SALES INVOICE	21,350.00	-	-	-
10	3	RCC VCH	-	-	11,700.00	-
11	4	VRTC VCH	-	-	1,000.00	-
12	5	PUFF PASW	-	-	-	4,300.00
13	6	MUFF SASW	-	-	-	900.00
14	7	ZUFF ZASW	-	-	-	1,200.00
15	TOTAL		21,350.00	118,000.00	12,700.00	6,400.00
RESULT

Cell Formulas
Range	Formula
C15:F15	C15	=SUM(C8:C14)

thanks

 

[HongRu]

Hello.

The cell "F12" of sheet "Result" when B4,C4 are empty should not be "7,137.00".
It's supposed to be "7,317.00".

Good luck.

 

[Amer Omar]

It's supposed to be "7,317.00".

yes , sorry about error !

 

[Amer Omar]

ok no answering so far so I posted here.
Merge partial character before specific word across sheets based on headers