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

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

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

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