Help with removing duplicate data based on value

M

Mchgh9

New Member
Joined
Feb 2, 2012
Messages
39
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Few questions:

Is "Promoter Reference" unique for each item? (Hence your example data just shows inspection status for 1 item i.e. 5895)

If answer to above is yes, then are you hoping to remove all rows that contains "5895" in the "Promoter Reference" column because it shows D/3 & Passed in the second line?
 
Upvote 0
platonic567 said:
Few questions:

Is "Promoter Reference" unique for each item? (Hence your example data just shows inspection status for 1 item i.e. 5895)

If answer to above is yes, then are you hoping to remove all rows that contains "5895" in the "Promoter Reference" column because it shows D/3 & Passed in the second line?
Click to expand...

Hi, the Promoter Ref is unique in the system where it exists but this is an export containing thousands of rows of data.

The below extract shows more of an overview

[TABLE="width: 779"]
<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]01/10/2012</SPAN>[/TD]
[TD]08:05</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Category A</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]PN001</SPAN>[/TD]
[TD]118064I</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]08:05</SPAN>[/TD]
[TD]RTN</SPAN>[/TD]
[TD]Routine Category A</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LT001</SPAN>[/TD]
[TD]120968I</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]09:02</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category A</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]PN001</SPAN>[/TD]
[TD]137036I</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]10:00</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit All Categories</SPAN>[/TD]
[TD]FAIL-HIGH RISK</SPAN>[/TD]
[TD]AA001</SPAN>[/TD]
[TD]18718I</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]13:14</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit All Categories</SPAN>[/TD]
[TD]FAIL-HIGH RISK</SPAN>[/TD]
[TD]AA001</SPAN>[/TD]
[TD]18718I</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]13:03</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]AA001</SPAN>[/TD]
[TD]19438R</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]09:00</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]341380H</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]09:00</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]349925H</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD] [/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]F12266D</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]10:45</SPAN>[/TD]
[TD]RTN</SPAN>[/TD]
[TD]Routine Category A</SPAN>[/TD]
[TD]FAIL-HIGH RISK</SPAN>[/TD]
[TD]AA001</SPAN>[/TD]
[TD]F1942R</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]13:00</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]RR001</SPAN>[/TD]
[TD]U3644201</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]09:00</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]U6171880</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]09:00</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Category B</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]U6239108</SPAN>[/TD]
[/TR]
[TR]
[TD]01/10/2012</SPAN>[/TD]
[TD]14:00</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LT001</SPAN>[/TD]
[TD]UC-C8504086A/02</SPAN>[/TD]
[/TR]
[TR]
[TD]02/10/2012</SPAN>[/TD]
[TD]11:28</SPAN>[/TD]
[TD]RTN</SPAN>[/TD]
[TD]Routine Non Categorised</SPAN>[/TD]
[TD]ABORTIVE</SPAN>[/TD]
[TD]GK001</SPAN>[/TD]
[TD]5700341265</SPAN>[/TD]
[/TR]
[TR]
[TD]02/10/2012</SPAN>[/TD]
[TD]09:22</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]AA001</SPAN>[/TD]
[TD]18718I</SPAN>[/TD]
[/TR]
[TR]
[TD]02/10/2012</SPAN>[/TD]
[TD]13:45</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion All Categories</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]AA001</SPAN>[/TD]
[TD]19438R</SPAN>[/TD]
[/TR]
[TR]
[TD]02/10/2012</SPAN>[/TD]
[TD]13:08</SPAN>[/TD]
[TD]RTN</SPAN>[/TD]
[TD]Routine Non Categorised</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]GK001</SPAN>[/TD]
[TD]331558H</SPAN>[/TD]
[/TR]
[TR]
[TD]02/10/2012</SPAN>[/TD]
[TD]12:05</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit All Categories</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]UE100</SPAN>[/TD]
[TD]69878I</SPAN>[/TD]
[/TR]
[TR]
[TD]02/10/2012</SPAN>[/TD]
[TD]10:39</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Category A</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]AA001</SPAN>[/TD]
[TD]F1942R</SPAN>[/TD]
[/TR]
[TR]
[TD]02/10/2012</SPAN>[/TD]
[TD]12:00</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Non-Categorised</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]LT001</SPAN>[/TD]
[TD]MW005059A</SPAN>[/TD]
[/TR]
[TR]
[TD]02/10/2012</SPAN>[/TD]
[TD]12:00</SPAN>[/TD]
[TD]D/3</SPAN>[/TD]
[TD]Defect Completion Non-Categorised</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]PR001</SPAN>[/TD]
[TD]U5896662/01</SPAN>[/TD]
[/TR]
[TR]
[TD]02/10/2012</SPAN>[/TD]
[TD]12:00</SPAN>[/TD]
[TD]D/2</SPAN>[/TD]
[TD]Defect Follow Up Non-Categorised</SPAN>[/TD]
[TD]ABORTIVE</SPAN>[/TD]
[TD]PR001</SPAN>[/TD]
[TD]U5896662/01</SPAN>[/TD]
[/TR]
[TR]
[TD]03/10/2012</SPAN>[/TD]
[TD]11:05</SPAN>[/TD]
[TD]TPR</SPAN>[/TD]
[TD]Third Party Report Category A</SPAN>[/TD]
[TD]FAIL-HIGH RISK</SPAN>[/TD]
[TD]PN001</SPAN>[/TD]
[TD]119156I</SPAN>[/TD]
[/TR]
[TR]
[TD]03/10/2012</SPAN>[/TD]
[TD] [/TD]
[TD]RTN</SPAN>[/TD]
[TD]Routine Category A</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]LT001</SPAN>[/TD]
[TD]121088I</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
So, if any Promoter ref (such as 5895 in the first example) has a row of data with a D/3 and also a "passed" then I want to remove every row, either automatically or by filtering so I am left with data that does not have a D/3 pass.

Basically once the D/3 is passed, the whole cycle is complete and it no longer needs to be recorded.
 
Upvote 0
Mchgh9 said:
So, if any Promoter ref (such as 5895 in the first example) has a row of data with a D/3 and also a "passed" then I want to remove every row, either automatically or by filtering so I am left with data that does not have a D/3 pass.

Basically once the D/3 is passed, the whole cycle is complete and it no longer needs to be recorded.
Click to expand...

As per your sample data, below are the rows you want deleted:
Rich (BB code): 
[TABLE="width: 836"]
<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="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]9:02</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category A</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]PN001</SPAN>
[/TD]
[TD]137036I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]13:03</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]19438R</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]13:45</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion All Categories</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]19438R</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]9:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category B</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LA001</SPAN>
[/TD]
[TD]341380H</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]9:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category B</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LA001</SPAN>
[/TD]
[TD]349925H</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD][/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category B</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LA001</SPAN>
[/TD]
[TD]F12266D</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]12:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Non-Categorised</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]PR001</SPAN>
[/TD]
[TD]U5896662/01</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]12:00</SPAN>
[/TD]
[TD]D/2</SPAN>
[/TD]
[TD]Defect Follow Up Non-Categorised</SPAN>
[/TD]
[TD]ABORTIVE</SPAN>
[/TD]
[TD]PR001</SPAN>
[/TD]
[TD]U5896662/01</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]9:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category B</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LA001</SPAN>
[/TD]
[TD]U6171880</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]9:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category B</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LA001</SPAN>
[/TD]
[TD]U6239108</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

After deletion you will only be left with these rows:

Rich (BB code): 
[TABLE="width: 836"]
<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="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]8:05</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Category A</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]PN001</SPAN>
[/TD]
[TD]118064I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]8:05</SPAN>
[/TD]
[TD]RTN</SPAN>
[/TD]
[TD]Routine Category A</SPAN>
[/TD]
[TD]FAIL-LOW RISK</SPAN>
[/TD]
[TD]LT001</SPAN>
[/TD]
[TD]120968I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]10:00</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit All Categories</SPAN>
[/TD]
[TD]FAIL-HIGH RISK</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]18718I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]13:14</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit All Categories</SPAN>
[/TD]
[TD]FAIL-HIGH RISK</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]18718I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]10:45</SPAN>
[/TD]
[TD]RTN</SPAN>
[/TD]
[TD]Routine Category A</SPAN>
[/TD]
[TD]FAIL-HIGH RISK</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]F1942R</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]13:00</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]RR001</SPAN>
[/TD]
[TD]U3644201</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]14:00</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>
[/TD]
[TD]FAIL-LOW RISK</SPAN>
[/TD]
[TD]LT001</SPAN>
[/TD]
[TD]UC-C8504086A/02</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]11:28</SPAN>
[/TD]
[TD]RTN</SPAN>
[/TD]
[TD]Routine Non Categorised</SPAN>
[/TD]
[TD]ABORTIVE</SPAN>
[/TD]
[TD]GK001</SPAN>
[/TD]
[TD="align: right"]5700341265</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]9:22</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]18718I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]13:08</SPAN>
[/TD]
[TD]RTN</SPAN>
[/TD]
[TD]Routine Non Categorised</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]GK001</SPAN>
[/TD]
[TD]331558H</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]12:05</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit All Categories</SPAN>
[/TD]
[TD]FAIL-LOW RISK</SPAN>
[/TD]
[TD]UE100</SPAN>
[/TD]
[TD]69878I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]10:39</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Category A</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]F1942R</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]12:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Non-Categorised</SPAN>
[/TD]
[TD]FAIL-LOW RISK</SPAN>
[/TD]
[TD]LT001</SPAN>
[/TD]
[TD]MW005059A</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]3-10-2012</SPAN>
[/TD]
[TD="align: right"]11:05</SPAN>
[/TD]
[TD]TPR</SPAN>
[/TD]
[TD]Third Party Report Category A</SPAN>
[/TD]
[TD]FAIL-HIGH RISK</SPAN>
[/TD]
[TD]PN001</SPAN>
[/TD]
[TD]119156I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]3-10-2012</SPAN>
[/TD]
[TD][/TD]
[TD]RTN</SPAN>
[/TD]
[TD]Routine Category A</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LT001</SPAN>
[/TD]
[TD]121088I</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

If the above results are correct, then I do have a method to do this however it involves quite a few steps.

Please let me know.
 
Upvote 0
platonic567 said:
As per your sample data, below are the rows you want deleted:
Rich (BB code): 
[TABLE="width: 836"]
<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="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]9:02</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category A</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]PN001</SPAN>
[/TD]
[TD]137036I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]13:03</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]19438R</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]13:45</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion All Categories</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]19438R</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]9:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category B</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LA001</SPAN>
[/TD]
[TD]341380H</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]9:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category B</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LA001</SPAN>
[/TD]
[TD]349925H</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD][/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category B</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LA001</SPAN>
[/TD]
[TD]F12266D</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]12:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Non-Categorised</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]PR001</SPAN>
[/TD]
[TD]U5896662/01</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]12:00</SPAN>
[/TD]
[TD]D/2</SPAN>
[/TD]
[TD]Defect Follow Up Non-Categorised</SPAN>
[/TD]
[TD]ABORTIVE</SPAN>
[/TD]
[TD]PR001</SPAN>
[/TD]
[TD]U5896662/01</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]9:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category B</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LA001</SPAN>
[/TD]
[TD]U6171880</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]9:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Category B</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LA001</SPAN>
[/TD]
[TD]U6239108</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

After deletion you will only be left with these rows:

Rich (BB code): 
[TABLE="width: 836"]
<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="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]8:05</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Category A</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]PN001</SPAN>
[/TD]
[TD]118064I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]8:05</SPAN>
[/TD]
[TD]RTN</SPAN>
[/TD]
[TD]Routine Category A</SPAN>
[/TD]
[TD]FAIL-LOW RISK</SPAN>
[/TD]
[TD]LT001</SPAN>
[/TD]
[TD]120968I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]10:00</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit All Categories</SPAN>
[/TD]
[TD]FAIL-HIGH RISK</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]18718I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]13:14</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit All Categories</SPAN>
[/TD]
[TD]FAIL-HIGH RISK</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]18718I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]10:45</SPAN>
[/TD]
[TD]RTN</SPAN>
[/TD]
[TD]Routine Category A</SPAN>
[/TD]
[TD]FAIL-HIGH RISK</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]F1942R</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]13:00</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]RR001</SPAN>
[/TD]
[TD]U3644201</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2012</SPAN>
[/TD]
[TD="align: right"]14:00</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>
[/TD]
[TD]FAIL-LOW RISK</SPAN>
[/TD]
[TD]LT001</SPAN>
[/TD]
[TD]UC-C8504086A/02</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]11:28</SPAN>
[/TD]
[TD]RTN</SPAN>
[/TD]
[TD]Routine Non Categorised</SPAN>
[/TD]
[TD]ABORTIVE</SPAN>
[/TD]
[TD]GK001</SPAN>
[/TD]
[TD="align: right"]5700341265</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]9:22</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Non Categorised</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]18718I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]13:08</SPAN>
[/TD]
[TD]RTN</SPAN>
[/TD]
[TD]Routine Non Categorised</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]GK001</SPAN>
[/TD]
[TD]331558H</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]12:05</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit All Categories</SPAN>
[/TD]
[TD]FAIL-LOW RISK</SPAN>
[/TD]
[TD]UE100</SPAN>
[/TD]
[TD]69878I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]10:39</SPAN>
[/TD]
[TD]D/1</SPAN>
[/TD]
[TD]Defect Joint Site Visit Category A</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]AA001</SPAN>
[/TD]
[TD]F1942R</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]12:00</SPAN>
[/TD]
[TD]D/3</SPAN>
[/TD]
[TD]Defect Completion Non-Categorised</SPAN>
[/TD]
[TD]FAIL-LOW RISK</SPAN>
[/TD]
[TD]LT001</SPAN>
[/TD]
[TD]MW005059A</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]3-10-2012</SPAN>
[/TD]
[TD="align: right"]11:05</SPAN>
[/TD]
[TD]TPR</SPAN>
[/TD]
[TD]Third Party Report Category A</SPAN>
[/TD]
[TD]FAIL-HIGH RISK</SPAN>
[/TD]
[TD]PN001</SPAN>
[/TD]
[TD]119156I</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]3-10-2012</SPAN>
[/TD]
[TD][/TD]
[TD]RTN</SPAN>
[/TD]
[TD]Routine Category A</SPAN>
[/TD]
[TD]PASSED</SPAN>
[/TD]
[TD]LT001</SPAN>
[/TD]
[TD]121088I</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

If the above results are correct, then I do have a method to do this however it involves quite a few steps.

Please let me know.
Click to expand...

Hi,

Yes this is correct. In your example, even the below row has been removed.
[TABLE="width: 836"]
<TBODY>[TR]
[TD="align: right"]2-10-2012</SPAN>
[/TD]
[TD="align: right"]12:00</SPAN>
[/TD]
[TD]D/2</SPAN>
[/TD]
[TD]Defect Follow Up Non-Categorised</SPAN>
[/TD]
[TD]ABORTIVE</SPAN>
[/TD]
[TD]PR001</SPAN>
[/TD]
[TD]U5896662/01
[/TD]
[/TR]
</TBODY>[/TABLE]


Thanks for your help

Dave
 
Upvote 0
Hello Dave,

Everything below is based on your sample data:

1. In H2, use below array entered formula (be sure to confirm with CTRL + SHIFT + ENTER)

Code: 
=ISNUMBER(MATCH(G2,IF($C$2:$C$26&$E$2:$E$26="D/3PASSED",$G$2:$G$26),0))

2. Now copy the formula down column H till the last row with data.

3. Copy the entire column H and paste as values.

4. Any row that has "TRUE" in column H needs to be deleted. It's up to you to sort or filter.

Please let me know if you have any questions.

Thanks,
Joseph
 
Upvote 0
Hi,

Thanks for your help. That works in as much as highlighting the data which has both a D/3 and a status of Passed. My problem is that I also need to remove every other row which has the same incident number but possibly a different status of d/1,d/2 or even a D/3 where the result is a fail. There can be an unlimited amount of these and I want to discount anything which has ever had a D/3 and passed to leave only those without this.

It may be I have to filter in a systematic way or that a formula can be used but I just can seem to work out how best to do it at the moment.

Thanks again,

Dave
 
Upvote 0
Mchgh9 said:
That works in as much as highlighting the data which has both a D/3 and a status of Passed.
Click to expand...

Now I am really curious to know whether you even tried my formula. If yes, then did you confirm the formula with CTRL + SHIFT + ENTER?

I added few more "D/3 & Passed" to your sample data and as you can see below my formula detected all instances of a Promoter Reference that has "D/3 & Passed".

I simply entered this formula in H2 and confirmed with CTRL + SHIFT + ENTER:

Code: 
=ISNUMBER(MATCH(G2,IF($C$2:$C$35&$E$2:$E$35="D/3PASSED",$G$2:$G$35),0))
Code: 
[TABLE="width: 991"]
<tbody>[TR]
[TD]Insp. Date[/TD]
[TD]Insp. Time[/TD]
[TD]Insp.[/TD]
[TD]Inspection Type Description[/TD]
[TD]Inspection Outcome[/TD]
[TD]Insp. OD[/TD]
[TD]Promoter Reference[/TD]
[TD]Test[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]8:05:00 AM[/TD]
[TD]D/1[/TD]
[TD]Defect Joint Site Visit Category A[/TD]
[TD]PASSED[/TD]
[TD]PN001[/TD]
[TD]118064I[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]8:05:00 AM[/TD]
[TD]RTN[/TD]
[TD]Routine Category A[/TD]
[TD]FAIL-LOW RISK[/TD]
[TD]LT001[/TD]
[TD]120968I[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]9:02:00 AM[/TD]
[TD]D/3[/TD]
[TD]Defect Completion Category A[/TD]
[TD]PASSED[/TD]
[TD]PN001[/TD]
[TD]137036I[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]10:00:00 AM[/TD]
[TD]D/1[/TD]
[TD]Defect Joint Site Visit All Categories[/TD]
[TD]FAIL-HIGH RISK[/TD]
[TD]AA001[/TD]
[TD]18718I[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]1:14:00 PM[/TD]
[TD]D/1[/TD]
[TD]Defect Joint Site Visit All Categories[/TD]
[TD]FAIL-HIGH RISK[/TD]
[TD]AA001[/TD]
[TD]18718I[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]1:03:00 PM[/TD]
[TD]D/1[/TD]
[TD]Defect Joint Site Visit Non Categorised[/TD]
[TD]PASSED[/TD]
[TD]AA001[/TD]
[TD]19438R[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]9:00:00 AM[/TD]
[TD]D/3[/TD]
[TD]Defect Completion Category B[/TD]
[TD]PASSED[/TD]
[TD]LA001[/TD]
[TD]341380H[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]9:00:00 AM[/TD]
[TD]D/3[/TD]
[TD]Defect Completion Category B[/TD]
[TD]PASSED[/TD]
[TD]LA001[/TD]
[TD]349925H[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD][/TD]
[TD]D/3[/TD]
[TD]Defect Completion Category B[/TD]
[TD]PASSED[/TD]
[TD]LA001[/TD]
[TD]F12266D[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]10:45:00 AM[/TD]
[TD]RTN[/TD]
[TD]Routine Category A[/TD]
[TD]FAIL-HIGH RISK[/TD]
[TD]AA001[/TD]
[TD]F1942R[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]1:00:00 PM[/TD]
[TD]D/1[/TD]
[TD]Defect Joint Site Visit Non Categorised[/TD]
[TD]PASSED[/TD]
[TD]RR001[/TD]
[TD]U3644201[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]9:00:00 AM[/TD]
[TD]D/3[/TD]
[TD]Defect Completion Category B[/TD]
[TD]PASSED[/TD]
[TD]LA001[/TD]
[TD]U6171880[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]9:00:00 AM[/TD]
[TD]D/3[/TD]
[TD]Defect Completion Category B[/TD]
[TD]PASSED[/TD]
[TD]LA001[/TD]
[TD]U6239108[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]2:00:00 PM[/TD]
[TD]D/1[/TD]
[TD]Defect Joint Site Visit Non Categorised[/TD]
[TD]FAIL-LOW RISK[/TD]
[TD]LT001[/TD]
[TD]UC-C8504086A/02[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]11:28:00 AM[/TD]
[TD]RTN[/TD]
[TD]Routine Non Categorised[/TD]
[TD]ABORTIVE[/TD]
[TD]GK001[/TD]
[TD]5700341265[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]9:22:00 AM[/TD]
[TD]D/1[/TD]
[TD]Defect Joint Site Visit Non Categorised[/TD]
[TD]PASSED[/TD]
[TD]AA001[/TD]
[TD]18718I[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]1:45:00 PM[/TD]
[TD]D/3[/TD]
[TD]Defect Completion All Categories[/TD]
[TD]PASSED[/TD]
[TD]AA001[/TD]
[TD]19438R[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]1:08:00 PM[/TD]
[TD]RTN[/TD]
[TD]Routine Non Categorised[/TD]
[TD]PASSED[/TD]
[TD]GK001[/TD]
[TD]331558H[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]12:05:00 PM[/TD]
[TD]D/1[/TD]
[TD]Defect Joint Site Visit All Categories[/TD]
[TD]FAIL-LOW RISK[/TD]
[TD]UE100[/TD]
[TD]69878I[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]10:39:00 AM[/TD]
[TD]D/1[/TD]
[TD]Defect Joint Site Visit Category A[/TD]
[TD]PASSED[/TD]
[TD]AA001[/TD]
[TD]F1942R[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]12:00:00 PM[/TD]
[TD]D/3[/TD]
[TD]Defect Completion Non-Categorised[/TD]
[TD]FAIL-LOW RISK[/TD]
[TD]LT001[/TD]
[TD]MW005059A[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]12:00:00 PM[/TD]
[TD]D/3[/TD]
[TD]Defect Completion Non-Categorised[/TD]
[TD]PASSED[/TD]
[TD]PR001[/TD]
[TD]U5896662/01[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]12:00:00 PM[/TD]
[TD]D/2[/TD]
[TD]Defect Follow Up Non-Categorised[/TD]
[TD]ABORTIVE[/TD]
[TD]PR001[/TD]
[TD]U5896662/01[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]3/10/2012[/TD]
[TD]11:05:00 AM[/TD]
[TD]TPR[/TD]
[TD]Third Party Report Category A[/TD]
[TD]FAIL-HIGH RISK[/TD]
[TD]PN001[/TD]
[TD]119156I[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]3/10/2012[/TD]
[TD]12:00:00 PM[/TD]
[TD]D/3[/TD]
[TD]Routine Category A[/TD]
[TD]PASSED[/TD]
[TD]LT001[/TD]
[TD]121088I[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]10:45:00 AM[/TD]
[TD]RTN[/TD]
[TD]Routine Category A[/TD]
[TD]FAIL-HIGH RISK[/TD]
[TD]AA001[/TD]
[TD]F1942R[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/10/2012[/TD]
[TD]2:00:00 PM[/TD]
[TD]D/1[/TD]
[TD]Defect Joint Site Visit Non Categorised[/TD]
[TD]FAIL-LOW RISK[/TD]
[TD]LT001[/TD]
[TD]UC-C8504086A/02[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]11:28:00 AM[/TD]
[TD]RTN[/TD]
[TD]Routine Non Categorised[/TD]
[TD]ABORTIVE[/TD]
[TD]GK001[/TD]
[TD]5700341265[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]9:22:00 AM[/TD]
[TD]D/1[/TD]
[TD]Defect Joint Site Visit Non Categorised[/TD]
[TD]PASSED[/TD]
[TD]AA001[/TD]
[TD]18718I[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]12:00:00 PM[/TD]
[TD]D/3[/TD]
[TD]Defect Completion Non-Categorised[/TD]
[TD]PASSED[/TD]
[TD]LT001[/TD]
[TD]MW005059A[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]12:00:00 PM[/TD]
[TD]D/1[/TD]
[TD]Defect Completion Non-Categorised[/TD]
[TD]FAILED[/TD]
[TD]PR001[/TD]
[TD]U5896662/01[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2/10/2012[/TD]
[TD]12:00:00 PM[/TD]
[TD]D/2[/TD]
[TD]Defect Follow Up Non-Categorised[/TD]
[TD]ABORTIVE[/TD]
[TD]PR001[/TD]
[TD]U5896662/01[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]3/10/2012[/TD]
[TD]11:05:00 AM[/TD]
[TD]D/3[/TD]
[TD]Third Party Report Category A[/TD]
[TD]PASSED[/TD]
[TD]PN001[/TD]
[TD]119156I[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]3/10/2012[/TD]
[TD]12:00:00 PM[/TD]
[TD]RTN[/TD]
[TD]Routine Category A[/TD]
[TD]PASSED[/TD]
[TD]LT001[/TD]
[TD]121088I[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi There.

Try this.


Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
499
XLfriend777
XLfriend777
A
  • Question Question
Create Duplicate records based on possible combinations of other values
Replies
7
Views
671
kvsrinivasamurthy
K
N
  • Question Question
Exclude data based on cell value
Replies
2
Views
502
Noxqss38242
N
D
  • Question Question
Move data from one sheet to mutlipe sheets based on sheet name
Replies
3
Views
673
Herakles
H

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top