If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel 2003 - deleting Duplicates under conditions
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 |
#2
|
|||
|
|||
Excel 2003 - deleting Duplicates under conditions
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 |
#3
|
|||
|
|||
Excel 2003 - deleting Duplicates under conditions
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 |
Thread Tools | |
Display Modes | |
|
|