Help, please

[esiason14]

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

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[bgeier]

In VBA you would get the players name, then setup a variable to store the number of games played, and store that to compare against all other occurrences of that players name.

Once the player name changes, you would clear the variables, and start a new search.

For example (this is a rough outline, not actual code)

Please Login or Register  to view this content.

NOTE THIS IS JUST AN EXAMPLE -- IT PROBABLY WILL NOT WORK AS IT IS WRITTEN

[ExcelEddie]

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

[kounoike]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[esiason14]

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.

[ExcelEddie]

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

[kounoike]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[ExcelEddie]

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

Excel Eddie