Match three columns with one column and pull numeric values

[abdo meghari]

Hello
I have In & Out Balance sheet ,every month I will insert three columns (ARRIVED & SALES , STOCK ) also every month will get new data for items sheet .
I want matching column B:D for In & Out Balance with column B for items
and populate values in last columns AARIVED& SALES(AARIVED& SALES will be change in location after add new month )
should update n & Out Balance sheet when update items sheet .
original data before fill

TIRES REPORT.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Category	Size	Pattern	Origin	Arrived	Sales	Stock	Arrived	Sales	Stock	Arrived	Sales	Stock
3	PSR (LRD)	175/70R13	B25	INDO	200	16	184	0	5	179			179
4		175/70R13	EP150	THI	0	0	0	0	0	10			10
5		185/70R13	EP150	INDO	0	0	0	0	0	0			0
6		175/65R14	EP150	INDO	20	0	20	10	0	30			30
7		175/70R14	EP150	THI	30	0	30	0	0	0			0
8		175/70R14	MY02	THI	110	0	110	0	5	105			105
9		185/65R14	TEC	THI	120	0	120	0	0	120			120
10		185/65R14	EP150	INDO	200	0	200	22	3	219			219
11		185/65R15	B250	JAP	0	0	0	0	0	0			0
12		195/60R15	AR20	INDO	11	0	11	0	0	11			11
13		195/60R15	EP150	THI	22	22	0	0	0	0			0
14		195/60R15	T001	JAP	0	0	0	0	0	0			0
15		195/60R15	150EZ	THI	0	0	0	0	0	0			0
16		195/65R15	MY02	THI	234	0	234	0	0	234			234
17		195/65R15	EP150	THI	444	0	444	0	0	444			444
18		195/65R15	EP150	JAP	44	0	44	0	0	44			44
19		195/65R15	T001	JAP	55	0	55	0	0	55			55
20		195/55R16	EP300	THI	0	0	0	0	0	0			0
21		205/55R16	RE003	THI	0	0	0	0	0	0			0
22		205/65R15	EP150	INDO	0	0	0	0	0	0			0
23		205/70R15	694	JAP	0	0	0	66	0	66			66
24		225/75R15C	T697	INDO	0	0	0	66	0	66			66
25		235/95R15C	D618	JAP	0	1	-1	0	0	-1			-1
26		255/70R15C	D840	THI	0	0	0	0	0	0			0
27		TTL			1490	39	1451	164	13	1582	0	0	1582
28	PSR (HRD)	215/45R17	T001	JAP	0	0	0	0	0	0			0
29		215/50R17	EP300	THI	34	0	34	0	0	34			34
30		215/55R17	GR90	INDO	44	0	44	0	0	44			44
31		285/30R20	RE0050A	JAP	0	0	0	0	0	0			0
32		315/35R20	SPORT	JAP	0	0	0	35	0	35			35
33		TTL			78	0	78	35	0	113	0	0	113
In & Out Balance

Cell Formulas
Range	Formula
J3,M28:M32,M3:M26,J28:J32,J8:J26,J5:J6	J3	=G3+H3-I3
E27:M27	E27	=SUM(E3:E26)
G28:G32,G3:G26	G3	=E3-F3
E33:M33	E33	=SUM(E28:E32)

TIRES REPORT.xlsm
	A	B	C	D
1	ITEM	Size	Arrived	Sales
2	1	195/60R15 T001 JAP	20	10
3	2	175/70R14 MY02 THI	110
4	3	205/55R16 RE003 THI	25	25
5	4	195/60R15 AR20 INDO	111	2
6	5	205/70R15 694 JAP		20
7	6	195/65R15 EP150 JAP	3	3
8	7	235/95R15C D618 JAP	22
9	8	285/30R20 RE0050A JAP	12
10	9	315/35R20 SPORT JAP		22
11
12
13
14
15
16
17
18
19
20
21
items

I expect result like this

TIRES REPORT.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Category	Size	Pattern	Origin	Arrived	Sales	Stock	Arrived	Sales	Stock	Arrived	Sales	Stock
3	PSR (LRD)	175/70R13	B25	INDO	200	16	184	0	5	179			179
4		175/70R13	EP150	THI	0	0	0	0	0	10			10
5		185/70R13	EP150	INDO	0	0	0	0	0	0			0
6		175/65R14	EP150	INDO	20	0	20	10	0	30			30
7		175/70R14	EP150	THI	30	0	30	0	0	0			0
8		175/70R14	MY02	THI	110	0	110	0	5	105	110		215
9		185/65R14	TEC	THI	120	0	120	0	0	120			120
10		185/65R14	EP150	INDO	200	0	200	22	3	219			219
11		185/65R15	B250	JAP	0	0	0	0	0	0			0
12		195/60R15	AR20	INDO	11	0	11	0	0	11	111	2	120
13		195/60R15	EP150	THI	22	22	0	0	0	0			0
14		195/60R15	T001	JAP	0	0	0	0	0	0	20	10	10
15		195/60R15	150EZ	THI	0	0	0	0	0	0			0
16		195/65R15	MY02	THI	234	0	234	0	0	234			234
17		195/65R15	EP150	THI	444	0	444	0	0	444			444
18		195/65R15	EP150	JAP	44	0	44	0	0	44	3	3	44
19		195/65R15	T001	JAP	55	0	55	0	0	55			55
20		195/55R16	EP300	THI	0	0	0	0	0	0			0
21		205/55R16	RE003	THI	0	0	0	0	0	0	25	25	0
22		205/65R15	EP150	INDO	0	0	0	0	0	0			0
23		205/70R15	694	JAP	0	0	0	66	0	66		20	46
24		225/75R15C	T697	INDO	0	0	0	66	0	66			66
25		235/95R15C	D618	JAP	0	1	-1	0	0	-1	22		21
26		255/70R15C	D840	THI	0	0	0	0	0	0			0
27		TTL			1490	39	1451	164	13	1582	291	60	1813
28	PSR (HRD)	215/45R17	T001	JAP	0	0	0	0	0	0			0
29		215/50R17	EP300	THI	34	0	34	0	0	34			34
30		215/55R17	GR90	INDO	44	0	44	0	0	44			44
31		285/30R20	RE0050A	JAP	0	0	0	0	0	0	12		12
32		315/35R20	SPORT	JAP	0	0	0	35	0	35		22	13
33		TTL			78	0	78	35	0	113	12	22	103
In & Out Balance

Cell Formulas
Range	Formula
J3,M28:M32,M3:M26,J28:J32,J8:J26,J5:J6	J3	=G3+H3-I3
E27:M27	E27	=SUM(E3:E26)
G28:G32,G3:G26	G3	=E3-F3
E33:M33	E33	=SUM(E28:E32)

thanks

 

[DanteAmor]

Hi @abdo meghari.

Note: The names of the sheets in the macro are: "In & Out Balance" and "items".

Please try this:

Sub MatchThreeColumns()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim f As Range, c As Range
  Dim dic As Object
  Dim kys As String
  Dim i As Long, col1 As Long, col2 As Long
  Dim a As Variant, b As Variant, itms As Variant
  
  Set sh1 = Sheets("In & Out Balance")
  Set sh2 = Sheets("items")
  Set dic = CreateObject("Scripting.Dictionary")
  
  a = sh1.Range("B3", sh1.Range("D" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 2)
  
  For i = 1 To UBound(a, 1)
    kys = Trim(a(i, 1)) & "|" & Trim(a(i, 2)) & "|" & Trim(a(i, 3))
    dic(kys) = i
  Next
  
  Set f = sh2.Range("1:1").Find(sh1.Range("K2").Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    col1 = f.Column
    
    Set f = sh2.Range("1:1").Find(sh1.Range("L2").Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      col2 = f.Column
      
      For Each c In sh2.Range("B2", sh2.Range("B" & Rows.Count).End(3))
        itms = Split(Trim(c.Value), " ")
        If UBound(itms) = 2 Then
          kys = itms(0) & "|" & itms(1) & "|" & itms(2)
          If dic.exists(kys) Then
            b(dic(kys), 1) = sh2.Cells(c.Row, col1)
            b(dic(kys), 2) = sh2.Cells(c.Row, col2)
          End If
        End If
      Next
      
      sh1.Range("K3").Resize(UBound(b), 2).Value = b
      
    Else
      MsgBox "Dos not exists Header " & sh1.Range("L2").Value
    End If
  Else
    MsgBox "Dos not exists Header " & sh1.Range("K2").Value
  End If
End Sub

---------------
I hope it helps you.
Cordially.
Dante Amor
---------------

 

[abdo meghari]

Hi Dante
there are two problems
1-when pull value should fill for the last columns ARRIVED & SALES . as I said

every month I will insert three columns (ARRIVED & SALES , STOCK )

it means the last columns could be N,O or Q,R for columns ARRIVED, SALES when every time insert three columns .it depends on matching for headers ARRIVED, SALES in last columns .
2- should not clear formula as in TTL row
thanks

 

[DanteAmor]

Hi Abdo:

You're right, it's my mistake, I hadn't understood the requirement correctly.

Please try this new macro:

Sub MatchThreeColumns_v2()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim f As Range
  Dim kys As String
  Dim i As Long, col1_1 As Long, col1_2 As Long, col2_1 As Long, col2_2 As Long
  
  Set sh1 = Sheets("In & Out Balance")
  Set sh2 = Sheets("items")
  
  Set f = sh1.Range("2:2").Find("Arrived", , xlValues, xlWhole, xlByColumns, xlPrevious, False)
  If f Is Nothing Then MsgBox "In & Out Balance. Does not exists Header Arrived": Exit Sub
  col1_1 = f.Column
  
  Set f = sh1.Range("2:2").Find("Sales", , xlValues, xlWhole, xlByColumns, xlPrevious, False)
  If f Is Nothing Then MsgBox "In & Out Balance. Does not exists Header Sales": Exit Sub
  col1_2 = f.Column
  
  Set f = sh2.Range("1:1").Find("Arrived", , xlValues, xlWhole, , , False)
  If f Is Nothing Then MsgBox "items. Does not exists Header Arrived": Exit Sub
  col2_1 = f.Column
        
  Set f = sh2.Range("1:1").Find("Sales", , xlValues, xlWhole, , , False)
  If f Is Nothing Then MsgBox "items. Does not exists Header Sales": Exit Sub
  col2_2 = f.Column
          
  For i = 3 To sh1.Range("B" & Rows.Count).End(3).Row
    If sh1.Range("B" & i).Value <> "TTL" And sh1.Range("C" & i).Value <> "" Then
      kys = Trim(sh1.Range("B" & i).Value) & " " & Trim(sh1.Range("C" & i).Value) & " " & Trim(sh1.Range("D" & i).Value)
      Set f = sh2.Range("B:B").Find(kys, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        sh1.Cells(i, col1_1).Value = sh2.Cells(f.Row, col2_1)
        sh1.Cells(i, col1_2).Value = sh2.Cells(f.Row, col2_2)
      End If
    End If
  Next
End Sub

Cordially.
Dante Amor

 

[abdo meghari]

Awesome !
thanks very much for your help