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

I was checking my post and your's caught my eye. I worked up an
example spreadsheet using a variety of data extraction techniques that
I haven't had to use in a few years. It involves named ranges,
vlookup, an obscure array function, etc. It is rather lengthy to type
out the process, but I could send it to you if you would like.

The only snag I hit was if a player played the exact same number of
games in the same season at two postions. It would pull the first one
in the list. I doubt this scenario occurs, but it should be noted.

ExcelEddie

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

Wow. Thanks, guys. I'll try this out later today. I appeciated th
tips/examples

ExcelEddie...yes, please send it to me. I would like to check it out

--
esiason1
-----------------------------------------------------------------------
esiason14's Profile: http://www.excelforum.com/member.php...fo&userid=3458
View this thread: http://www.excelforum.com/showthread.php?threadid=54359

I'm sure the VBA examples above will work very well. However, this
example works without any coding for those who are not comfortable with
VBA. It brought back great memories of data extraction techniques from
my pre VBA days (oops, I'm showing my age :-)!

Please let me know an email, FTP site, or some other method of contact
and I would be happy to forward it to you!

ExcelEddie

Sorry, my typo. i mean position column name as Played column name.
so, in your case, it might be

Const pos As String = "e" 'Change to position column name

should be

Const pos As String = "g" 'Change to Played column name

in my guess.

also, this can't deal so fast with data if many. i think using a query
is the way to go.

keizi

"esiason14"
wrote in message
...

Wow. Thanks, guys. I'll try this out later today. I appeciated the
tips/examples!

ExcelEddie...yes, please send it to me. I would like to check it out.


--
esiason14
----------------------------------------------------------------------
--
esiason14's Profile:
http://www.excelforum.com/member.php...o&userid=34587
View this thread:
http://www.excelforum.com/showthread...hreadid=543597

Hey, esiason14, how did you come out with your spreadsheet?
I still have this example spreadsheet with nowhere to send it :-(

Excel Eddie