Hi

I am using Excel 2003 and have a data list displaying Reference Numbers and
Test Results.

If reference numbers fail a test they will be retested until they finally
pass the test, therefore there are multiple records for these reference
number.

Once the reference number has PASSED I want to delete ALL (Pass and
Fails)occurences of this reference number.

Any ideas?

Very grateful for any suggestions.
--
Thanks
Cath

Hi Cathy,
Here is a very unsophisticated macro to do the trick

Sub Tryme()
Range("A1").Select
Set myrange = Range("A2:A120")
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=myrange _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=myrange _
, SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange myrange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Worksheets("Sheet1").Select
mylast = Cells(Cells.Rows.Count, "A").End(xlUp).Row
testID = Range("A2")
J = 1
Do While J <= mylast
If UCase(Cells(J, 2)) = "PASS" Then
myflag = True
testID = Cells(J, 1)
Range(Cells(J, 1), Cells(J, 2)) = ""
Else
If myflag And Cells(J, 1) = testID Then
Range(Cells(J, 1), Cells(J, 2)) = ""
Else
myflag = False
End If
End If
J = J + 1
Loop
Call Macro6
End Sub
Sub Macro6()
Range("A1:B120").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("A2:A120") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:B120")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Cath" wrote in message
...
Hi

I am using Excel 2003 and have a data list displaying Reference Numbers
and
Test Results.

If reference numbers fail a test they will be retested until they finally
pass the test, therefore there are multiple records for these reference
number.

Once the reference number has PASSED I want to delete ALL (Pass and
Fails)occurences of this reference number.

Any ideas?

Very grateful for any suggestions.
--
Thanks
Cath

With your reference numbers in column A and test results in column B use this
in column C

=IF(SUMPRODUCT(--($A$2:$A$500=A3),--($B$2:$B$500="Pass"))0,"Delete","Keep")

Then you can filter on column C and delete the ones that say Delete


"Cath" wrote:

Hi

I am using Excel 2003 and have a data list displaying Reference Numbers and
Test Results.

If reference numbers fail a test they will be retested until they finally
pass the test, therefore there are multiple records for these reference
number.

Once the reference number has PASSED I want to delete ALL (Pass and
Fails)occurences of this reference number.

Any ideas?

Very grateful for any suggestions.
--
Thanks
Cath