match data between two sheets based on four columns instead of one column

[Alaa mg]

match data between two sheets based on four columns instead one column
hi
I have this code works very well . it match data between two sheets based on COL A and update the quantity in sheet inventory after matching it will subtracting the value in COL E from sheet sales .
so what I want update the code to become matching based on COL B,C,D,E together and update the quantity in COL F in sheet INVENTORY as in the code
NOTE: the structure into two sheets are the same thing

Sub qtyin()
Dim X, cell As Range
With Sheets("Sales")
    For Each cell In .Range("A20:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
        With Sheets("Result")
            X = Application.Match(cell, .Range("A:A"), 0)
            If Not IsError(X) Then
                .Range("E" & X) = .Range("E" & X).Value - cell.Offset(, 4).Value
            End If
        End With
    Next cell
End With
End Sub

sheet sale

update q.xlsm
	A	B	C	D	E	F
19	item	cct	brand	type	origin	quantity
20	1	c-food	tune	180wg	thi	1
21	2	c-wat	tune	180wg	indo	2
22	3	c-food	tune	180wg	indo	1
SALES

update q.xlsm
	A	B	C	D	E	F
1	item	cct	brand	type	origin	quantity
2	1	c-food	tune	180wg	thi	234
3	2	c-wat	tune	180wg	indo	123
4	3	c-food	tune	180wg	indo	200
inventory

 

[DanteAmor]

Try this:

Sub update_Inventory()
  Dim sh As Worksheet
  Dim dic As Object
  Dim a As Variant
  Dim c As Range
  Dim s As String
  Dim i As Long
  
  Set sh = Sheets("Inventory")
  Set dic = CreateObject("Scripting.Dictionary")
  a = sh.Range("B1:E" & sh.Range("B" & Rows.Count).End(3).Row).Value
  
  For i = 2 To UBound(a, 1)
    dic(a(i, 1) & "|" & a(i, 2) & "|" & a(i, 3) & "|" & a(i, 4)) = i
  Next
  
  For Each c In Sheets("Sales").Range("B2", Sheets("Sales").Range("B" & Rows.Count).End(xlUp))
    s = c.Value & "|" & c.Offset(, 1).Value & "|" & c.Offset(, 2).Value & "|" & c.Offset(, 3).Value
    If dic.exists(s) Then
      i = dic(s)
      sh.Range("F" & i).Value = sh.Range("F" & i).Value - c.Offset(, 4)
    End If
  Next
End Sub

 

[Alaa mg]

fantastic ! may you make the code in worksheet event change in sheet sale , then automatically change the QTY in sheet inventory instead of use button