Matching duplicates

[Cruiser69]

Hi all.
Could anyone please help matching duplicates using VBA if this is possible

We scan codes against lots we have entered in columns D & E. We then get a master sheet sent which may contain extra lots we have not scanned or we may have scanned extra lots which are not on the sheet.
In the example below you can see all match apart from lot 4 which was not scanned

I want to add a line on columns D & E and add the corresponding number in.
Lot 6 which was scanned is not on the master sheet so D & E will need deleting

This is the final outcome I require.

Thanks for looking.

Regards,

Graham

 

[RobP]

Hi,

I suggest to try this on DUMMY data, or a COPY of your workbook - as it has the power to make a real mess of your data.

First it checks to see if Scanned Lots are inside the "Master Lot" Col C. If not, it removes them.

Then it checks each Master Lot against the scan Lot, and adds rows in.

Good Luck

Rob

Sub check_Lots()

Dim vMasterLot As Variant
Dim vScannedLot As Variant

scannedlast = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row  'Col4 to find last row
masterlast = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row  'Col3 to find last row

vMasterLot = Range("c3", "c" & masterlast)

For x = scannedlast To 3 Step -1 'first check all Scanned Lots to see if they are in Master Lot List.
     sExtraLot = True
     scanpart = Range("D" & x) 'store cell data for quicker array loop speed below
    
      For arr = LBound(vMasterLot) To UBound(vMasterLot) 'check Master Lot list to see if scanned lot exists or not.
        If vMasterLot(arr, 1) = scanpart Then
            sExtraLot = False
            Exit For 'exit arr loop if Lot exists in Master list
        End If
      Next arr
       
    If sExtraLot Then Range("D" & x, "E" & x).Delete  'remove the 2 unwanted columns not appearing in Master Lot.
 Next x
  

scannedlast = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row  'Reset Scanned Last in case of removals from previous
vScannedLot = Range("D3", "D" & scannedlast)

For x = 3 To masterlast  'now check all Master Lots to see if they are in Scanned Lot List.
     sExtraLot = True
      masterpart = Range("C" & x) 'store cell data for quicker array loop speed below
     
      For arr = LBound(vScannedLot) To UBound(vScannedLot) 'check Master see if scanned lot exists or not.
        If vScannedLot(arr, 1) = masterpart Then
            sExtraLot = False
            Exit For 'exit arr loop if Lot exists in Master list
        End If
      Next arr
       
    If sExtraLot Then
        Range("D" & x, "E" & x).Insert  'insert partial row, pushing data downward
        Range("D" & x) = Range("C" & x) 'add Master Lot no. back in to Col D
    End If
 Next x
   

End Sub

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1
2	Sale	Id	Master Lot	Scanned Lot	Code				Sale	Id	Master Lot	Scanned Lot	Code
3	M1234	1234	1	1	V1234				M1234	1234	1	1	V1234
4	M1234	1234	2	2	V1334				M1234	1234	2	2	V1334
5	M1234	1234	3	3	V2123				M1234	1234	3	3	V2123
6	M1234	1234	4	4					M1234	1234	4	5	V2322
7	M1234	1234	5	5	V2322				M1234	1234	5	6	V5454
8	M1234	1234	7	7	V4323				M1234	1234	7	7	V4323
9	M1234	1234	8	8	V1233				M1234	1234	8	8	V1233
Sheet1