Help with removing duplicate data based on value

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

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
 

platonic567

Board Regular
Joined
Feb 21, 2014
Messages
153
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?
 

Mchgh9

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

Insp. Date</SPAN>Insp. Time</SPAN>Insp.</SPAN>Inspection Type Description</SPAN>Inspection Outcome</SPAN>Insp. OD</SPAN>Promoter Reference</SPAN>
01/10/2012</SPAN>08:05</SPAN>D/1</SPAN>Defect Joint Site Visit Category A</SPAN>PASSED</SPAN>PN001</SPAN>118064I</SPAN>
01/10/2012</SPAN>08:05</SPAN>RTN</SPAN>Routine Category A</SPAN>FAIL-LOW RISK</SPAN>LT001</SPAN>120968I</SPAN>
01/10/2012</SPAN>09:02</SPAN>D/3</SPAN>Defect Completion Category A</SPAN>PASSED</SPAN>PN001</SPAN>137036I</SPAN>
01/10/2012</SPAN>10:00</SPAN>D/1</SPAN>Defect Joint Site Visit All Categories</SPAN>FAIL-HIGH RISK</SPAN>AA001</SPAN>18718I</SPAN>
01/10/2012</SPAN>13:14</SPAN>D/1</SPAN>Defect Joint Site Visit All Categories</SPAN>FAIL-HIGH RISK</SPAN>AA001</SPAN>18718I</SPAN>
01/10/2012</SPAN>13:03</SPAN>D/1</SPAN>Defect Joint Site Visit Non Categorised</SPAN>PASSED</SPAN>AA001</SPAN>19438R</SPAN>
01/10/2012</SPAN>09:00</SPAN>D/3</SPAN>Defect Completion Category B</SPAN>PASSED</SPAN>LA001</SPAN>341380H</SPAN>
01/10/2012</SPAN>09:00</SPAN>D/3</SPAN>Defect Completion Category B</SPAN>PASSED</SPAN>LA001</SPAN>349925H</SPAN>
01/10/2012</SPAN> D/3</SPAN>Defect Completion Category B</SPAN>PASSED</SPAN>LA001</SPAN>F12266D</SPAN>
01/10/2012</SPAN>10:45</SPAN>RTN</SPAN>Routine Category A</SPAN>FAIL-HIGH RISK</SPAN>AA001</SPAN>F1942R</SPAN>
01/10/2012</SPAN>13:00</SPAN>D/1</SPAN>Defect Joint Site Visit Non Categorised</SPAN>PASSED</SPAN>RR001</SPAN>U3644201</SPAN>
01/10/2012</SPAN>09:00</SPAN>D/3</SPAN>Defect Completion Category B</SPAN>PASSED</SPAN>LA001</SPAN>U6171880</SPAN>
01/10/2012</SPAN>09:00</SPAN>D/3</SPAN>Defect Completion Category B</SPAN>PASSED</SPAN>LA001</SPAN>U6239108</SPAN>
01/10/2012</SPAN>14:00</SPAN>D/1</SPAN>Defect Joint Site Visit Non Categorised</SPAN>FAIL-LOW RISK</SPAN>LT001</SPAN>UC-C8504086A/02</SPAN>
02/10/2012</SPAN>11:28</SPAN>RTN</SPAN>Routine Non Categorised</SPAN>ABORTIVE</SPAN>GK001</SPAN>5700341265</SPAN>
02/10/2012</SPAN>09:22</SPAN>D/1</SPAN>Defect Joint Site Visit Non Categorised</SPAN>PASSED</SPAN>AA001</SPAN>18718I</SPAN>
02/10/2012</SPAN>13:45</SPAN>D/3</SPAN>Defect Completion All Categories</SPAN>PASSED</SPAN>AA001</SPAN>19438R</SPAN>
02/10/2012</SPAN>13:08</SPAN>RTN</SPAN>Routine Non Categorised</SPAN>PASSED</SPAN>GK001</SPAN>331558H</SPAN>
02/10/2012</SPAN>12:05</SPAN>D/1</SPAN>Defect Joint Site Visit All Categories</SPAN>FAIL-LOW RISK</SPAN>UE100</SPAN>69878I</SPAN>
02/10/2012</SPAN>10:39</SPAN>D/1</SPAN>Defect Joint Site Visit Category A</SPAN>PASSED</SPAN>AA001</SPAN>F1942R</SPAN>
02/10/2012</SPAN>12:00</SPAN>D/3</SPAN>Defect Completion Non-Categorised</SPAN>FAIL-LOW RISK</SPAN>LT001</SPAN>MW005059A</SPAN>
02/10/2012</SPAN>12:00</SPAN>D/3</SPAN>Defect Completion Non-Categorised</SPAN>PASSED</SPAN>PR001</SPAN>U5896662/01</SPAN>
02/10/2012</SPAN>12:00</SPAN>D/2</SPAN>Defect Follow Up Non-Categorised</SPAN>ABORTIVE</SPAN>PR001</SPAN>U5896662/01</SPAN>
03/10/2012</SPAN>11:05</SPAN>TPR</SPAN>Third Party Report Category A</SPAN>FAIL-HIGH RISK</SPAN>PN001</SPAN>119156I</SPAN>
03/10/2012</SPAN> RTN</SPAN>Routine Category A</SPAN>PASSED</SPAN>LT001</SPAN>121088I</SPAN>

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

Mchgh9

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

platonic567

Board Regular
Joined
Feb 21, 2014
Messages
153
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.
As per your sample data, below are the rows you want deleted:
Rich (BB code):
Insp. Date</SPAN> Insp. Time</SPAN> Insp.</SPAN> Inspection Type Description</SPAN> Inspection Outcome</SPAN> Insp. OD</SPAN> Promoter Reference</SPAN>
1-10-2012</SPAN> 9:02</SPAN> D/3</SPAN> Defect Completion Category A</SPAN> PASSED</SPAN> PN001</SPAN> 137036I</SPAN>
1-10-2012</SPAN> 13:03</SPAN> D/1</SPAN> Defect Joint Site Visit Non Categorised</SPAN> PASSED</SPAN> AA001</SPAN> 19438R</SPAN>
2-10-2012</SPAN> 13:45</SPAN> D/3</SPAN> Defect Completion All Categories</SPAN> PASSED</SPAN> AA001</SPAN> 19438R</SPAN>
1-10-2012</SPAN> 9:00</SPAN> D/3</SPAN> Defect Completion Category B</SPAN> PASSED</SPAN> LA001</SPAN> 341380H</SPAN>
1-10-2012</SPAN> 9:00</SPAN> D/3</SPAN> Defect Completion Category B</SPAN> PASSED</SPAN> LA001</SPAN> 349925H</SPAN>
1-10-2012</SPAN> D/3</SPAN> Defect Completion Category B</SPAN> PASSED</SPAN> LA001</SPAN> F12266D</SPAN>
2-10-2012</SPAN> 12:00</SPAN> D/3</SPAN> Defect Completion Non-Categorised</SPAN> PASSED</SPAN> PR001</SPAN> U5896662/01</SPAN>
2-10-2012</SPAN> 12:00</SPAN> D/2</SPAN> Defect Follow Up Non-Categorised</SPAN> ABORTIVE</SPAN> PR001</SPAN> U5896662/01</SPAN>
1-10-2012</SPAN> 9:00</SPAN> D/3</SPAN> Defect Completion Category B</SPAN> PASSED</SPAN> LA001</SPAN> U6171880</SPAN>
1-10-2012</SPAN> 9:00</SPAN> D/3</SPAN> Defect Completion Category B</SPAN> PASSED</SPAN> LA001</SPAN> U6239108</SPAN>
<TBODY> </TBODY>
After deletion you will only be left with these rows:

Rich (BB code):
Insp. Date</SPAN> Insp. Time</SPAN> Insp.</SPAN> Inspection Type Description</SPAN> Inspection Outcome</SPAN> Insp. OD</SPAN> Promoter Reference</SPAN>
1-10-2012</SPAN> 8:05</SPAN> D/1</SPAN> Defect Joint Site Visit Category A</SPAN> PASSED</SPAN> PN001</SPAN> 118064I</SPAN>
1-10-2012</SPAN> 8:05</SPAN> RTN</SPAN> Routine Category A</SPAN> FAIL-LOW RISK</SPAN> LT001</SPAN> 120968I</SPAN>
1-10-2012</SPAN> 10:00</SPAN> D/1</SPAN> Defect Joint Site Visit All Categories</SPAN> FAIL-HIGH RISK</SPAN> AA001</SPAN> 18718I</SPAN>
1-10-2012</SPAN> 13:14</SPAN> D/1</SPAN> Defect Joint Site Visit All Categories</SPAN> FAIL-HIGH RISK</SPAN> AA001</SPAN> 18718I</SPAN>
1-10-2012</SPAN> 10:45</SPAN> RTN</SPAN> Routine Category A</SPAN> FAIL-HIGH RISK</SPAN> AA001</SPAN> F1942R</SPAN>
1-10-2012</SPAN> 13:00</SPAN> D/1</SPAN> Defect Joint Site Visit Non Categorised</SPAN> PASSED</SPAN> RR001</SPAN> U3644201</SPAN>
1-10-2012</SPAN> 14:00</SPAN> D/1</SPAN> Defect Joint Site Visit Non Categorised</SPAN> FAIL-LOW RISK</SPAN> LT001</SPAN> UC-C8504086A/02</SPAN>
2-10-2012</SPAN> 11:28</SPAN> RTN</SPAN> Routine Non Categorised</SPAN> ABORTIVE</SPAN> GK001</SPAN> 5700341265</SPAN>
2-10-2012</SPAN> 9:22</SPAN> D/1</SPAN> Defect Joint Site Visit Non Categorised</SPAN> PASSED</SPAN> AA001</SPAN> 18718I</SPAN>
2-10-2012</SPAN> 13:08</SPAN> RTN</SPAN> Routine Non Categorised</SPAN> PASSED</SPAN> GK001</SPAN> 331558H</SPAN>
2-10-2012</SPAN> 12:05</SPAN> D/1</SPAN> Defect Joint Site Visit All Categories</SPAN> FAIL-LOW RISK</SPAN> UE100</SPAN> 69878I</SPAN>
2-10-2012</SPAN> 10:39</SPAN> D/1</SPAN> Defect Joint Site Visit Category A</SPAN> PASSED</SPAN> AA001</SPAN> F1942R</SPAN>
2-10-2012</SPAN> 12:00</SPAN> D/3</SPAN> Defect Completion Non-Categorised</SPAN> FAIL-LOW RISK</SPAN> LT001</SPAN> MW005059A</SPAN>
3-10-2012</SPAN> 11:05</SPAN> TPR</SPAN> Third Party Report Category A</SPAN> FAIL-HIGH RISK</SPAN> PN001</SPAN> 119156I</SPAN>
3-10-2012</SPAN> RTN</SPAN> Routine Category A</SPAN> PASSED</SPAN> LT001</SPAN> 121088I</SPAN>
<TBODY> </TBODY>
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.
 

Mchgh9

New Member
Joined
Feb 2, 2012
Messages
39
As per your sample data, below are the rows you want deleted:
Rich (BB code):
Insp. Date</SPAN> Insp. Time</SPAN> Insp.</SPAN> Inspection Type Description</SPAN> Inspection Outcome</SPAN> Insp. OD</SPAN> Promoter Reference</SPAN>
1-10-2012</SPAN> 9:02</SPAN> D/3</SPAN> Defect Completion Category A</SPAN> PASSED</SPAN> PN001</SPAN> 137036I</SPAN>
1-10-2012</SPAN> 13:03</SPAN> D/1</SPAN> Defect Joint Site Visit Non Categorised</SPAN> PASSED</SPAN> AA001</SPAN> 19438R</SPAN>
2-10-2012</SPAN> 13:45</SPAN> D/3</SPAN> Defect Completion All Categories</SPAN> PASSED</SPAN> AA001</SPAN> 19438R</SPAN>
1-10-2012</SPAN> 9:00</SPAN> D/3</SPAN> Defect Completion Category B</SPAN> PASSED</SPAN> LA001</SPAN> 341380H</SPAN>
1-10-2012</SPAN> 9:00</SPAN> D/3</SPAN> Defect Completion Category B</SPAN> PASSED</SPAN> LA001</SPAN> 349925H</SPAN>
1-10-2012</SPAN> D/3</SPAN> Defect Completion Category B</SPAN> PASSED</SPAN> LA001</SPAN> F12266D</SPAN>
2-10-2012</SPAN> 12:00</SPAN> D/3</SPAN> Defect Completion Non-Categorised</SPAN> PASSED</SPAN> PR001</SPAN> U5896662/01</SPAN>
2-10-2012</SPAN> 12:00</SPAN> D/2</SPAN> Defect Follow Up Non-Categorised</SPAN> ABORTIVE</SPAN> PR001</SPAN> U5896662/01</SPAN>
1-10-2012</SPAN> 9:00</SPAN> D/3</SPAN> Defect Completion Category B</SPAN> PASSED</SPAN> LA001</SPAN> U6171880</SPAN>
1-10-2012</SPAN> 9:00</SPAN> D/3</SPAN> Defect Completion Category B</SPAN> PASSED</SPAN> LA001</SPAN> U6239108</SPAN>
<TBODY> </TBODY>
After deletion you will only be left with these rows:

Rich (BB code):
Insp. Date</SPAN> Insp. Time</SPAN> Insp.</SPAN> Inspection Type Description</SPAN> Inspection Outcome</SPAN> Insp. OD</SPAN> Promoter Reference</SPAN>
1-10-2012</SPAN> 8:05</SPAN> D/1</SPAN> Defect Joint Site Visit Category A</SPAN> PASSED</SPAN> PN001</SPAN> 118064I</SPAN>
1-10-2012</SPAN> 8:05</SPAN> RTN</SPAN> Routine Category A</SPAN> FAIL-LOW RISK</SPAN> LT001</SPAN> 120968I</SPAN>
1-10-2012</SPAN> 10:00</SPAN> D/1</SPAN> Defect Joint Site Visit All Categories</SPAN> FAIL-HIGH RISK</SPAN> AA001</SPAN> 18718I</SPAN>
1-10-2012</SPAN> 13:14</SPAN> D/1</SPAN> Defect Joint Site Visit All Categories</SPAN> FAIL-HIGH RISK</SPAN> AA001</SPAN> 18718I</SPAN>
1-10-2012</SPAN> 10:45</SPAN> RTN</SPAN> Routine Category A</SPAN> FAIL-HIGH RISK</SPAN> AA001</SPAN> F1942R</SPAN>
1-10-2012</SPAN> 13:00</SPAN> D/1</SPAN> Defect Joint Site Visit Non Categorised</SPAN> PASSED</SPAN> RR001</SPAN> U3644201</SPAN>
1-10-2012</SPAN> 14:00</SPAN> D/1</SPAN> Defect Joint Site Visit Non Categorised</SPAN> FAIL-LOW RISK</SPAN> LT001</SPAN> UC-C8504086A/02</SPAN>
2-10-2012</SPAN> 11:28</SPAN> RTN</SPAN> Routine Non Categorised</SPAN> ABORTIVE</SPAN> GK001</SPAN> 5700341265</SPAN>
2-10-2012</SPAN> 9:22</SPAN> D/1</SPAN> Defect Joint Site Visit Non Categorised</SPAN> PASSED</SPAN> AA001</SPAN> 18718I</SPAN>
2-10-2012</SPAN> 13:08</SPAN> RTN</SPAN> Routine Non Categorised</SPAN> PASSED</SPAN> GK001</SPAN> 331558H</SPAN>
2-10-2012</SPAN> 12:05</SPAN> D/1</SPAN> Defect Joint Site Visit All Categories</SPAN> FAIL-LOW RISK</SPAN> UE100</SPAN> 69878I</SPAN>
2-10-2012</SPAN> 10:39</SPAN> D/1</SPAN> Defect Joint Site Visit Category A</SPAN> PASSED</SPAN> AA001</SPAN> F1942R</SPAN>
2-10-2012</SPAN> 12:00</SPAN> D/3</SPAN> Defect Completion Non-Categorised</SPAN> FAIL-LOW RISK</SPAN> LT001</SPAN> MW005059A</SPAN>
3-10-2012</SPAN> 11:05</SPAN> TPR</SPAN> Third Party Report Category A</SPAN> FAIL-HIGH RISK</SPAN> PN001</SPAN> 119156I</SPAN>
3-10-2012</SPAN> RTN</SPAN> Routine Category A</SPAN> PASSED</SPAN> LT001</SPAN> 121088I</SPAN>
<TBODY> </TBODY>
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.
Hi,

Yes this is correct. In your example, even the below row has been removed.
2-10-2012</SPAN>
12:00</SPAN>
D/2</SPAN>
Defect Follow Up Non-Categorised</SPAN>
ABORTIVE</SPAN>
PR001</SPAN>
U5896662/01

<TBODY>
</TBODY>


Thanks for your help

Dave
 

platonic567

Board Regular
Joined
Feb 21, 2014
Messages
153
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
 

Mchgh9

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

platonic567

Board Regular
Joined
Feb 21, 2014
Messages
153
That works in as much as highlighting the data which has both a D/3 and a status of Passed.
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]
 

sastoka

Board Regular
Joined
Jun 14, 2014
Messages
193
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
 

Forum statistics

Threads
1,082,043
Messages
5,362,836
Members
400,694
Latest member
Sofie17

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top