Help with removing duplicate data based on value

[Mchgh9]

Hi,

I have a large amount of data that contains an inspection status of various types e.g D/1,D/2, D/3, RTN, TPR, there is also a cell which contains an inspection result of pass/fail.

Each line of data refers to an item held in a database with a reference number. This data can have multiple inspections for D/1, D/2 etc

What I need to do is to remove all items that have had a D/3 with a result of pass.

Here is an example of the data

[TABLE="width: 771"]
<TBODY>[TR]
[TD]Insp. Date</SPAN>[/TD]
[TD]Insp. Time</SPAN>[/TD]
[TD]Insp.</SPAN>[/TD]
[TD]Inspection Type Description</SPAN>[/TD]
[TD]Inspection Outcome</SPAN>[/TD]
[TD]Insp. OD</SPAN>[/TD]
[TD]Promoter Reference</SPAN>[/TD]
[/TR]
[TR]
[TD]12/02/2014</SPAN>[/TD]
[TD]14:04</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]5895</SPAN>[/TD]
[/TR]
[TR]
[TD]08/10/2012</SPAN>[/TD]
[TD]14:04</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]5895</SPAN>[/TD]
[/TR]
[TR]
[TD]01/11/2012</SPAN>[/TD]
[TD]16:00</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]5895</SPAN>[/TD]
[/TR]
[TR]
[TD]15/11/2012</SPAN>[/TD]
[TD]16:00</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]5895</SPAN>[/TD]
[/TR]
[TR]
[TD]11/12/2012</SPAN>[/TD]
[TD]10:00</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]5895</SPAN>[/TD]
[/TR]
[TR]
[TD]24/12/2012</SPAN>[/TD]
[TD]10:00</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]5895</SPAN>[/TD]
[/TR]
[TR]
[TD]18/01/2013</SPAN>[/TD]
[TD]09:03</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]5895</SPAN>[/TD]
[/TR]
[TR]
[TD]01/02/2013</SPAN>[/TD]
[TD]09:03</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]5895</SPAN>[/TD]
[/TR]
[TR]
[TD]26/02/2013</SPAN>[/TD]
[TD]15:04</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]5895</SPAN>[/TD]
[/TR]
[TR]
[TD]12/03/2013</SPAN>[/TD]
[TD]15:05</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]5895</SPAN>[/TD]
[/TR]
[TR]
[TD]08/04/2013</SPAN>[/TD]
[TD]08:04</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]5895</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

Because there is a line of data which reference has a D/3 Pass I want to remove all of these entries from the sheet.

I just want to be left with data that has never recieved a D/3 in column "Insp" and Passed in "Inspection Data".

I tried to use formatting to display duplicates but this highlighted data which there were duplicates for but no D/3, passes.

I know this is a tough one to explain so any help greatly appreciated.

Dave

 

[Mchgh9]

Hi There.

Try this.

Sub FIND_MATCH_DELETE()

Dim r1 As Range
Dim r2 As Range
Dim LastRow As Long


LastRow = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row

For Each r1 In Range("C2:C" & LastRow)
    If r1.Value = "D/3" Then
        If r1.Offset(0, 2).Value = "PASSED" Then
             Valr1 = r1.Offset(0, 4).Value
             For Each r2 In Range("G2:G" & LastRow)
                If r2.Value = Valr1 Then
                    Rows(r2.Row).ClearContents
                End If
            Next r2
        End If
    End If
Next r1
    
End Sub

Hi,

Do I just need to put this into a new macro?

I haven't done much with code before.

Thanks for your help

Dave