ExcelBanter - Delete Unnecessary Rows

How about a macro?

If that's ok, try this against a copy of your data--or close the file without
saving. The macro destroys a lot of data when it runs!

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'sort by name (ascending)
'then by type (ascending)
'then by score (descending--highest score on top)
With .Range(.Cells(FirstRow, "A"), .Cells(LastRow, "C"))
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Key2:=.Columns(2), Order2:=xlAscending, _
Key3:=.Columns(3), Order3:=xlDescending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With

'remove duplicate rows based on name & type.
'we delete from the bottom up. And since the scores are in
'descending order, we get rid of the lower score rows
For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
If .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'duplicate in both name and type
.Rows(iRow).Delete
End If
End If
Next iRow

'reset last row -- after deleting rows, it's changed
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'look for rows where the value in column A is used exactly 3 times
'if it's different from 3, then delete the row.
For iRow = LastRow To FirstRow Step -1
If Application.CountIf(.Range("A:a"), _
.Cells(iRow, "A").Value) < 3 Then
.Rows(iRow).Delete
End If
Next iRow

End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Steve wrote:

I am trying to "clean up" a data sheet so that I can run a drag down command.
The data looks like this:

Name Type Score
John AM 4
John AM 5
John LI 3
John RI 7
John RI 7
John RI 6
Dave AM 4
George AM 6
George LI 5
George RI 4

I would like to turn that into this

Name Type Score
John AM 5
John LI 3
John RI 7
George AM 6
George LI 5
George RI 4

So basically there are three types; AM, LI, RI and I want to take the
highest score for each type and delete any other score. The catch is, if the
person doesn't all three have 3 types, then I have to delete them. Can
anyone help me?

--

Dave Peterson