complicated project to add new data before specific row for each part

[Mussa]

hello

I need help from experts. this project is very complicated . so what I want match column C for sheets (rop,sol,sall,slm) with columns B,C,D together if they are matched then should pull the values and summing when there are repeated items into sheets (rop,sol,sall,slm) but when pull or summing data should be based on heades for two columns (BTR,SALR) becuase I will insert three columns every month (BTR,SALR,QTY) ,and if there is new item is exised in sheets(rop,sol,sall,slm) but not existed in sheet DATA , then should add before TOTAL row for each part . how know which is part should add it ? . it depends on match column B for sheets(rop,sol,sall,slm) with column A in sheet DATA . the item for every part in column A starts from row 1 and finish up to TOTAL row when matching . but if the items counts are different for the others have already divided in sheet DATA .as you see most of items are divided contains 4 items in sheet DATA and also in sheets((rop,sol,sall,slm) into column C .,so in this case split firs two item in column B, and second item in column C and third item in column D , but somtimes contains five or six items then when split for sheet DATA column B = first two items , column C = second two items , column D = last one item . as to six items then when split in sheet DATA column B = first two items , column C = second two items , column D = last two items

see the rows 3,4 (rop,sol) and compare with divied in sheet data in row14,19

if what I ask for it is impossible or any idea can be easy or better than what I ask it , all of my ears.
if this is not clear just infor me
thanks

before

ITEM (2).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	DEL NO	BATCH NO	TTL	TT1	BTR	SALR	QTY	BTR	SALR	QTY	BTR	SALR	QTY	BTR	SALR	QTY	BTR	SALR	QTY
3	CC	BT CC-1	SS-1	TRU			0			0			0			0			0
4		BT CC-1	SS-1	LTR			0			0			0			0			0
5		BT CC-2	SS-2	FG			0			0			0			0			0
6		TOTAL			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	CCN	CT CC-1	SS-1	TRR			0			0			0			0			0
8		CT CC-1	SS-1	LTR			0			0			0			0			0
9		TOTAL			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	CD	DD CS-1	LL-1	RRL			0			0			0			0			0
11		DD CS-2	LL-2	TTY			0			0			0			0			0
12		DD CS-3	LL-3	MMW			0			0			0			0			0
13		DD CS-4	LL-4	NNW			0			0			0			0			0
14		TOTAL			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	CCL	LC CC-2	SS-2	LTR			0			0			0			0			0
16		TOTAL			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17	CCM	LM CC-3	SS-3	LTR			0			0			0			0			0
18		TOTAL			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
data

Cell Formulas
Range	Formula
E6:S6	E6	=SUM(E3:E5)
E9:S9	E9	=SUM(E7:E8)
E14:S14	E14	=SUM(E10:E13)
E18:S18,E16:S16	E16	=SUM(E15:E15)
S17,S15,S10:S13,S7:S8,S3:S5,P17,P15,P10:P13,P7:P8,P3:P5,M17,M15,M10:M13,M7:M8,M3:M5,J17,J15,J10:J13,J7:J8,J3:J5,G17,G15,G10:G13,G7:G8,G3:G5	G3	=E3-F3

ITEM (2).xlsm
	A	B	C	D
1	ITEM	DEL NO	BATCH NO	BTR
2	1	CCM	LM CC-3 SS-3 LTR	110
3	2	CCM	LM CC-4 MM SS-3 LTR	111
4	3	CC	BT CC-1 SS-1 TRU	200
5	4	CC	BT CC-1 SS-1 LTR	120
6	5	CC	BT CC-2 SS-2 FG	110
7	6	CCN	CT CC-1 SS-1 TRR	100
8	7	CCN	CT CC-1 SS-1 LTR	110
9	8	CD	DD CS-1 LL-1 RRL	200
10	9	CD	DD CS-2 LL-2 TTY	220
11	10	CD	DD CS-3 LL-3 MMW	200
12	11	CD	DD CS-4 LL-4 NNW	100
13	12	CCL	LC CC-2 SS-2 LTR	110
rop

ITEM (2).xlsm
	A	B	C	D
1	ITEM	DEL NO	BATCH NO	BTR
2	1	CC	BT CC-1 SS-1 TRU	200
3	2	CC	BT CC-1 SS-1 LTR	120
4	3	CCN	CT CC-1 SS-1 LTR	110
5	4	CD	DD CS-2 PP2 LL-1 SS20 RRL	200
6	5	CC	BT CC-2 SS-2 FG	110
7	6	CCN	CT CC-1 SS-1 TRR	100
sol

ITEM (2).xlsm
	A	B	C	D
1	ITEM	DEL NO	BATCH NO	SALR
2	1	CC	BT CC-1 SS-1 TRU	200
3	2	CC	BT CC-1 SS-1 LTR	120
4	3	CCN	CT CC-1 SS-1 LTR	110
5	4	CD	DD CS-2 PP2 LL-1 SS20 RRL	200
6	5	CC	BT CC-2 SS-2 FG	110
7	6	CCN	CT CC-1 SS-1 TRR	100
SALL

ITEM (2).xlsm
	A	B	C	D	E
1	ITEM	DEL NO	BATCH NO	SALR	BTR
2	1	CCM	LM CC-3 SS-3 LTR	110	200
3	2	CCM	LM CC-4 MM SS-3 LTR	111	120
4	3	CC	BT CC-1 SS-1 TRU	200	110
5	4	CC	BT CC-1 SS-1 LTR	120	200
6	5	CC	BT CC-2 SS-2 FG	110	110
SLM

after when run from the first time

ITEM (2).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	DEL NO	BATCH NO	TTL	TT1	BTR	SALR	QTY	BTR	SALR	QTY	BTR	SALR	QTY	BTR	SALR	QTY	BTR	SALR	QTY
3	CC	BT CC-1	SS-1	TRU	510	400	110			0			0			0			0
4		BT CC-1	SS-1	LTR	440	240	200			0			0			0			0
5		BT CC-2	SS-2	FG	330	220	110			0			0			0			0
6		TOTAL			1280	860	420	0	0	0	0	0	0	0	0	0	0	0	0
7	CCN	CT CC-1	SS-1	TRR	200	100	100			0			0			0			0
8		CT CC-1	SS-1	LTR	220	110	110			0			0			0			0
9		TOTAL			420	210	210	0	0	0	0	0	0	0	0	0	0	0	0
10	CD	DD CS-1	LL-1	RRL	200		200			0			0			0			0
11		DD CS-2	LL-2	TTY	220		220			0			0			0			0
12		DD CS-3	LL-3	MMW	200		200			0			0			0			0
13		DD CS-4	LL-4	NNW	100		100			0			0			0			0
14		DD CS-2	PP2 LL-1	SS20 RRL	200	200	0			0			0			0			0
15		TOTAL			920	200	720	0	0	0	0	0	0	0	0	0	0	0	0
16	CCL	LC CC-2	SS-2	LTR	110		110			0			0			0			0
17		TOTAL			110	0	110	0	0	0	0	0	0	0	0	0	0	0	0
18	CCM	LM CC-3	SS-3	LTR	310	110	200			0			0			0			0
19		LM CC-4	MM SS-3	LTR	231	111	120			0			0			0			0
20		TOTAL			541	221	320	0	0	0	0	0	0	0	0	0	0	0	0
data

Cell Formulas
Range	Formula
E6:S6	E6	=SUM(E3:E5)
E20:S20,E9:S9	E9	=SUM(E7:E8)
E15:S15	E15	=SUM(E10:E14)
E17:S17	E17	=SUM(E16:E16)
S18:S19,S16,S10:S14,S7:S8,S3:S5,P18:P19,P16,P10:P14,P7:P8,P3:P5,M18:M19,M16,M10:M14,M7:M8,M3:M5,J18:J19,J16,J10:J14,J7:J8,J3:J5,G18:G19,G16,G10:G14,G7:G8,G3:G5	G3	=E3-F3

if run again should move empty columns BTR,SALR whether add new data before TOTAL row for each part or pull and summing values for data have already existed

ITEM (2).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	DEL NO	BATCH NO	TTL	TT1	BTR	SALR	QTY	BTR	SALR	QTY	BTR	SALR	QTY	BTR	SALR	QTY	BTR	SALR	QTY
3	CC	BT CC-1	SS-1	TRU	510	400	110	510	400	110			0			0			0
4		BT CC-1	SS-1	LTR	440	240	200	440	240	200			0			0			0
5		BT CC-2	SS-2	FG	330	220	110	330	220	110			0			0			0
6		TOTAL			1280	860	420	1280	860	420	0	0	0	0	0	0	0	0	0
7	CCN	CT CC-1	SS-1	TRR	200	100	100	200	100	100			0			0			0
8		CT CC-1	SS-1	LTR	220	110	110	220	110	110			0			0			0
9		TOTAL			420	210	210	420	210	210	0	0	0	0	0	0	0	0	0
10	CD	DD CS-1	LL-1	RRL	200		200	200		200			0			0			0
11		DD CS-2	LL-2	TTY	220		220	220		220			0			0			0
12		DD CS-3	LL-3	MMW	200		200	200		200			0			0			0
13		DD CS-4	LL-4	NNW	100		100	100		100			0			0			0
14		DD CS-2	PP2 LL-1	SS20 RRL	200	200	0	200	200	0			0			0			0
15		TOTAL			920	200	720	920	200	720	0	0	0	0	0	0	0	0	0
16	CCL	LC CC-2	SS-2	LTR	110		110	110		110			0			0			0
17		TOTAL			110	0	110	110	0	110	0	0	0	0	0	0	0	0	0
18	CCM	LM CC-3	SS-3	LTR	310	110	200	310	110	200			0			0			0
19		LM CC-4	MM SS-3	LTR	231	111	120	231	111	120			0			0			0
20		TOTAL			541	221	320	541	221	320	0	0	0	0	0	0	0	0	0
data

Cell Formulas
Range	Formula
E6:S6	E6	=SUM(E3:E5)
E20:S20,E9:S9	E9	=SUM(E7:E8)
E15:S15	E15	=SUM(E10:E14)
E17:S17	E17	=SUM(E16:E16)
S18:S19,S16,S10:S14,S7:S8,S3:S5,P18:P19,P16,P10:P14,P7:P8,P3:P5,M18:M19,M16,M10:M14,M7:M8,M3:M5,J18:J19,J16,J10:J14,J7:J8,J3:J5,G18:G19,G16,G10:G14,G7:G8,G3:G5	G3	=E3-F3

and so on every run macro should fill empty columns BTR,SALR

 

[Mussa]

 

[Mussa]

bump