delete 12000 rows contains duplicates items

[leap out]

Hi

I need macro to delete about 12000 rows for the duplicates items between two sheets . but should match duplicates items for the whole range . if the values are different for the same item should not be deleted.

OFFTHEL.xlsm
	A	B	C
1	ITEM	GOODS	QTY
2	1	10W40 12x1L Q8 EU	200
3	2	10W40 12x1L CAS SU	120
4	3	10W40 12x1L ENI IT	300
5	4	OL-115W40 12x1L CAS SU**2	240
6	5	MLO-1 10W40 208L TT/L CAS SU	150
7	6	10W40 208L ENI IT	160
8	7	5W30 208L Q8 EU	200
9	8	OIL/M 10W40 4x4L CAS TRM SU N3-90	120
rep

OFFTHEL.xlsm
	A	B	C
1	ITEM	GOODS	QTY
2	1	10W40 12x1L Q8 EU	100
3	2	MLO-1 10W40 208L TT/L CAS SU	120
4	3	10W40 208L ENI IT	160
5	4	5W30 208L Q8 EU	200
6	5	OIL/M 10W40 4x4L CAS TRM SU N3-90	120
7	6	10W40 12x1L CAS SU	120
8	7	10W40 12x1L ENI IT	300
9	8	OL-115W40 12x1L CAS SU**2	240
10	9	OL-115W40 12x1L CAS SU**3	1000
11	10	OL-115W40 12x1L CAS SU**4	1200
12	11	OL-115W40 12x1L CAS SU**5	120
13	12	OL-115W40 12x1L CAS SU**6	200
stock

delete the items should be from sheet STOCK
the result

OFFTHELP.xlsm
	A	B	C
1	ITEM	GOODS	QTY
2	1	10W40 12x1L Q8 EU	100
3	2	MLO-1 10W40 208L TT/L CAS SU	120
4	3	OL-115W40 12x1L CAS SU**3	1000
5	4	OL-115W40 12x1L CAS SU**4	1200
6	5	OL-115W40 12x1L CAS SU**5	120
7	6	OL-115W40 12x1L CAS SU**6	200
output

D=h:c|v:m|fz:10pt|cls:ww]200[/XD][/XR][/RANGE]

 

[DanteAmor]

Try this:

Sub deleteduplicates()
  Dim dic As Object
  Dim a, b, c
  Dim i As Long, j As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("rep").Range("A2", Sheets("rep").Range("C" & Rows.Count).End(3)).Value
  b = Sheets("stock").Range("A2", Sheets("stock").Range("C" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(b, 1), 1 To 3)
  
  For i = 1 To UBound(a, 1)
    dic(a(i, 2) & "|" & a(i, 3)) = Empty
  Next
  
  For i = 1 To UBound(b)
    If Not dic.exists(b(i, 2) & "|" & b(i, 3)) Then
      j = j + 1
      c(j, 1) = j
      c(j, 2) = b(i, 2)
      c(j, 3) = b(i, 3)
    End If
  Next
  
  Sheets("output").Range("A2").Resize(j, 3).Value = c
End Sub

 

[leap out]

wow ! that's very fantastic !
many thanks for this code

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.