For example, try this
Sub mytest()
Const id As String = "a" 'Change to Playerid column name
Const pos As String = "e" 'Change to position column name
Const strow As Long = 2 'Change to start row number
Dim n As Long, i As Long
Dim yr As Range
Dim str1 As String, str2 As String, str3 As String
Dim str4 As String, str5 As String
On Error Resume Next
Set yr = Application.InputBox _
("Select a cell with a given year", Type:=8)
If yr Is Nothing Then
Exit Sub
End If
On Error GoTo 0
Set yr = yr.Resize(1, 1)
str5 = yr.Address
Application.ScreenUpdating = False
n = Cells(Cells.Rows.Count, id).End(xlUp).Row
str1 = Range(Cells(strow, id), Cells(n, id)).Address
str2 = Range(Cells(strow, pos), Cells(n, pos)).Address
str4 = Range(Cells(strow, yr.Column), Cells(n, yr.Column)) _
.Address
For i = strow To n
str3 = Cells(i, "a").Address
If Application.Max(Evaluate _
("(" & str1 & "=" & str3 & ")*" & "(" & str2 & ")*" _
& "(" & str4 & "=" & str5 & ")")) _
< Cells(i, pos) Then
Rows(i).Hidden = True
Else
End If
Next
End Sub
keizi
"esiason14"
wrote in message
...
Hi, I have a huge spreadsheet of baseball players....Some players
played
multiple positions during a single season. What I want to do is find
the
duplicate player ids for a given year and then find the position they
played most for that year and delete the other records. For example
(below)...this player played 4 different positions in 1905. I would
like it to keep the record where he played 58 games (the most for that
season) and delete the rest. Could someone please at least get me on
the right track. Thanks
Code:
--------------------
ID YEAR # TEAM POSITION GAMES PLAYED
--------------------
Code:
--------------------
bemisha01 1905 1 CLE 1B 1
bemisha01 1905 1 CLE 2B 4
bemisha01 1905 1 CLE 3B 2
bemisha01 1905 1 CLE C 58
--------------------
--
esiason14
----------------------------------------------------------------------
--
esiason14's Profile:
http://www.excelforum.com/member.php...o&userid=34587
View this thread:
http://www.excelforum.com/showthread...hreadid=543597