match & copy and sum values into last threes columns across multiple sheets

[abdo meghari]

I have many sheets . the result should show the values in sheet report into last three columns which contains headers (ARRIVED , SALES ) .so should match columns B,C,D in sheet REPORT with the others sheets . let's take column ARRIVED after matching column B,C,D should sum the values in column E in sheet (PURCHASE,RETURNS) and column F in sheet(SALES). for instance 175/70R14 MY02 THI (200+10+100)=310.
as to column SALES after matching column B,C,D .should sum the values in column E in sheet (SAL1,SALES) .for instance
825R16 R180 JAP (50+200)=250.
NOTE: every month I issue three columns . so should search the two last columns (arived,sales) and fill the values under theses columns .
also I highlighted the values which match and addion or summing and it will increase data in sheet report about 1500 rows

report (11) .xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
1				JULY			AUGUST			SEPTEMBER
2	Size	Pattern	Origin	Arrived	Sales	Stock	Arrived	Sales	Stock	Arrived	Sales	Stock
3	175/70R13	B25	INDO			-			-			-
4	175/70R13	EP150	THI	8	4	4		4	-			-
5	185/70R13	EP150	INDO	8		8	4	8	4			4
6	175/65R14	EP150	INDO			-			-			-
7	175/70R14	EP150	THI	4		4			4			4
8	175/70R14	MY02	THI			-			-			-
9	185/65R14	TEC	THI	9		9		8	1			1
10	185/65R14	EP150	INDO	16	13	3	5	8	-			-
11	185/65R14	150EZ	INDO			-						-
12	185/65R15	TC10	INDO			-			-			-
13	185/65R15	T005	INDO			-			-			-
14	185/65R15	T01	JAP			-			-			-
15	185/65R15	B250	JAP			-			-			-
16	195/60R15	AR20	INDO			-			-			-
17	195/60R15	EP150	THI		4	4-	4		-			-
18	195/60R15	T001	JAP			-			-			-
19	195/60R15	150EZ	THI			-
20	255/70R16	H005	THI			-			-			-
21	265/70R16	D850	THI			-			-			-
22	265/70R16	D840	THI			-			-			-
23	275/70R16	H005	THI			-			-			-
24	275/70R16	D694	JAP	4		4		4	-			-
25	LT285/75R16	AT001	JAP			-			-			-
26	TTL			49	21	28	13	32	9	-	-	9
27	285/60R18	D850	JAP			-			-			-
28	245/40R20	RE050	JAP			-			-			-
29	265/50R20	SPORT	JAP			-			-			-
30	275/30R20	RE050	JAP			-			-			-
31	275/40R20	DHP	JAP			-			8			9
32	275/55R20	D680	JAP			-			-			-
33	285/30R20	RE0050A	JAP			-			-			-
34	315/35R20	SPORT	JAP			-			-			-
35	TTL			-	-	-	-	-	8	-	-	9
36	750R16	VSJ	THI	20	10	10	55		65			65
37	825R16	R180	THI			-	1		1			1
38	TTL			20	10	10	56	-	66	-	-	66
REPORT

Cell Formulas
Range	Formula
M36:M37,M32:M34,J36:J37,J32:J34,M27:M30,M20:M25,J27:J30,J20:J25,M3:M18,J12:J18,J3:J10	J3	=G3+H3-I3
E26:M26	E26	=SUM(E3:E25)
E35:M35	E35	=SUM(E27:E34)
G36:G37,G27:G34,G3:G25	G3	=E3-F3
E38:M38	E38	=SUM(E36:E37)

report (11) .xlsm
	A	B	C	D	E
1	ITEM	BRAND	TYPE	ORIGIN	QTY
2	1	175/70R14	EP150	THI	200.00
3	2	175/70R14	MY02	THI	250.00
4	3	185/65R14	EP150	IND	300.00
5	4	185/65R14	TEC	THI	400.00
6	5	185/65R15	T005	IND	500.00
7	6	195/60 R15	EP150	THI	500.00
8	7	750R16	VSJ	THI	1000.00
9	8	825R16	R180	THI	1200.00
PURCHASE

report (11) .xlsm
	A	B	C	D	E
1	ITEM	BRAND	TYPE	ORIGIN	QTY
2	1	175/70R14	EP150	THI	10.00
3	2	175/70R14	MY02	THI	20.00
4	3	185/65R14	TEC	THI	15.00
5	4	185/65R15	T005	IND	4.00
6	5	750R16	VSJ	THI	20.00
7	6	825R16	R180	THI	15.00
RETURNS

report (11) .xlsm
	A	B	C	D	E
1	ITEM	BRAND	TYPE	ORIGIN	QTY
2	1	750R16	VSJ	THI	50.00
3	2	825R16	R180	THI	50.00
SAL1

report (11) .xlsm
	A	B	C	D	E	F
1	ITEM	BRAND	TYPE	ORIGIN	SALES	RETURNS
2	1	175/70R14	EP150	THI	150.00	100.00
3	2	175/70R14	MY02	THI		10.00
4	3	185/65R14	EP150	IND	15.00
5	4	185/65R14	TEC	THI	20.00
6	5	185/65R15	T005	IND	100.00	50.00
7	6	750R16	VSJ	THI		10.00
8	7	825R16	R180	THI	200.00	10.00
SALES

should be result in sheet REPORT

report (11) .xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
1				JULY			AUGUST			SEPTEMBER
2	Size	Pattern	Origin	Arrived	Sales	Stock	Arrived	Sales	Stock	Arrived	Sales	Stock
3	175/70R13	B25	INDO			-			-			-
4	175/70R13	EP150	THI	8	4	4		4	-			-
5	185/70R13	EP150	INDO	8		8	4	8	4			4
6	175/65R14	EP150	INDO			-			-			-
7	175/70R14	EP150	THI	4		4			4	310.00	150	164
8	175/70R14	MY02	THI			-			-	280.00		280
9	185/65R14	TEC	THI	9		9		8	1	415.00	20	396
10	185/65R14	EP150	INDO	16	13	3	5	8	-	300.00	15	285
11	185/65R14	150EZ	INDO			-						-
12	185/65R15	TC10	INDO			-			-			-
13	185/65R15	T005	INDO			-			-	554.00	100	454
14	185/65R15	T01	JAP			-			-			-
15	185/65R15	B250	JAP			-			-			-
16	195/60R15	AR20	INDO			-			-			-
17	195/60R15	EP150	THI		4	4-	4		-	500.00		500
18	195/60R15	T001	JAP			-			-			-
19	195/60R15	150EZ	THI			-
20	255/70R16	H005	THI			-			-			-
21	265/70R16	D850	THI			-			-			-
22	265/70R16	D840	THI			-			-			-
23	275/70R16	H005	THI			-			-			-
24	275/70R16	D694	JAP	4		4		4	-			-
25	LT285/75R16	AT001	JAP			-			-			-
26	TTL			49	21	28	13	32	9	2,359	285	2,083
27	285/60R18	D850	JAP			-			-			-
28	245/40R20	RE050	JAP			-			-			-
29	265/50R20	SPORT	JAP			-			-			-
30	275/30R20	RE050	JAP			-			-			-
31	275/40R20	DHP	JAP			-			8			9
32	275/55R20	D680	JAP			-			-			-
33	285/30R20	RE0050A	JAP			-			-			-
34	315/35R20	SPORT	JAP			-			-			-
35	TTL			-	-	-	-	-	8	-	-	9
36	750R16	VSJ	THI	20	10	10	55		65	1,030	50	1,045
37	825R16	R180	THI			-	1		1	1,225	250	976
38	TTL			20	10	10	56	-	66	2,255	300	2,021
REPORT

Cell Formulas
Range	Formula
M36:M37,M32:M34,J36:J37,J32:J34,M27:M30,M20:M25,J27:J30,J20:J25,M3:M18,J12:J18,J3:J10	J3	=G3+H3-I3
E26:M26	E26	=SUM(E3:E25)
E35:M35	E35	=SUM(E27:E34)
G36:G37,G27:G34,G3:G25	G3	=E3-F3
E38:M38	E38	=SUM(E36:E37)

*** is short of JAPAN
thanks in advance

 

[abdo meghari]

bump

 

[offthelip]

I have had a go at solving your problem but it is not complete and doesn't give the correct asnwer but it might get you started. I have run out of time.

Sub testr()
   Dim Dic As Object
   Set Dic = CreateObject("Scripting.dictionary")
    Dic.CompareMode = vbTextCompare

With Worksheets("PURCHASE")
 lastpur = .Cells(Rows.Count, "A").End(xlUp).Row
 purarr = .Range(.Cells(1, 1), .Cells(lastpur, 5))
End With

With Worksheets("RETURNS")
 lastret = .Cells(Rows.Count, "A").End(xlUp).Row
 retarr = .Range(.Cells(1, 1), .Cells(lastret, 5))
End With

With Worksheets("SALES")
 lastsal = .Cells(Rows.Count, "A").End(xlUp).Row
 salarr = .Range(.Cells(1, 1), .Cells(lastsal, 6))
End With

With Worksheets("SAL1")
 lastS1 = .Cells(Rows.Count, "A").End(xlUp).Row
 S1arr = .Range(.Cells(1, 1), .Cells(lastS1, 5))
End With

With Worksheets("REPORT")
 lastrep = .Cells(Rows.Count, "B").End(xlUp).Row
 reparr = .Range(.Cells(1, 2), .Cells(lastrep, 4))
 outarr = .Range(.Cells(1, 11), .Cells(lastrep, 12))

' load dictionary
   For i = 3 To UBound(reparr, 1)
      tt = Trim(reparr(i, 1)) & Trim(reparr(i, 2)) & Trim(reparr(i, 3)) ' concatenate columns B,C and D
      Dic(tt) = i  ' add them all to the dictionary
   Next i
  
For j = 2 To lastpur
     tp = Trim(purarr(j, 2)) & Trim(purarr(j, 3)) & Trim(purarr(j, 4)) ' concatenate columns B,C and D
       If Dic.exists(tp) Then
       outarr(Dic(tp), 1) = purarr(j, 5)
       End If
Next j
For j = 1 To lastret
     tp = retarr(j, 2) & retarr(j, 3) & retarr(j, 4) ' concatenate columns B,C and D
     If Dic.exists(tp) Then
     outarr(Dic(tp), 1) = outarr(Dic(tp), 1) + retarr(j, 5)
     End If
Next j

For j = 1 To lastsal
     tp = salarr(j, 2) & salarr(j, 3) & salarr(j, 4) ' concatenate columns B,C and D
       If Dic.exists(tp) Then
       outarr(Dic(tp), 1) = outarr(Dic(tp), 1) + salarr(j, 6)
       outarr(Dic(tp), 2) = salarr(j, 5)
       End If
Next j

For j = 1 To lastS1
     tp = S1arr(j, 2) & S1arr(j, 3) & S1arr(j, 4) ' concatenate columns B,C and D
       If Dic.exists(tp) Then
       outarr(Dic(tp), 2) = outarr(Dic(tp), 2) + S1arr(j, 5)
       End If
Next j
 .Range(.Cells(1, 11), .Cells(lastrep, 12)) = outarr
End With
End Sub

 

[abdo meghari]

thanks for your trying , but it just bring the values form sheet purchase without summing from the others sheets, also some items are missed . the code doesn't bring all of the values and summing from the others sheets .