Help with renaming data in a new column

[Ed]

Hello, I hope this is not too confusing: I am putting several
spreadsheets together to use during my fantasy baseball draft. I'm
creating a sheet for each team, and on each team's page I'm trying to
sort their draft results by position. I have a formula that can do
this, but in order to use it I need to uniquely identify each player's
position.


For example, if a team's first four picks are (Players in Column A on
the sheet, their respective positions in Column B)

Helton 1B
Pujols 1B, OF
Sexson 1B
Abreu OF


then I would like a column C to read the result from B, and if B
contains a result that is equal to a position of an earlier drafted
player, then I would like a unique name for each position in the new
column. That seems extremely unclear, thanks so much for even reading
this far, here's how I want column C to look in the example above:

1B
1B-2, OF
1B-3
OF-2

I think I could string some IFs and OR functions together in a
simplistic fashion if there were only one or two positions involved;
theproblem is that there are actually 9 different positions: C, 1B,
2B, 3B, SS, OF, DH, SP, RP, and I want the second, third and fourth,
etc. players at each such position to be labeled pos-2, pos-3, pos-4,
respectively.

Thank you for any assistance you can provide!

 

[Dave Peterson]

I think that this'll work:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myCurPos As Variant
Dim PrevCellPos As Variant
Dim iCtr As Long
Dim myStr As String
Dim myCount As Long


With ActiveSheet
.Range("c:c").ClearContents
FirstRow = 2
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = FirstRow To LastRow
If iRow = FirstRow Then
.Cells(iRow, "C").Value = .Cells(iRow, "B").Value
Else
myStr = ""
myCurPos = Split97(Application.Substitute _
(.Cells(iRow, "B").Value, " ", ""), ",")
For iCtr = LBound(myCurPos) To UBound(myCurPos)
myCount = 1 + Application.CountIf(.Cells(FirstRow, "B") _
.Resize(iRow - FirstRow), _
"*" & myCurPos(iCtr) & "*")
myStr = myStr & myCurPos(iCtr)
If myCount > 1 Then
myStr = myStr & "-" & myCount
End If
myStr = myStr & ", "
Next iCtr

myStr = Left(myStr, Len(myStr) - 2) 'chop off that extra ", "
.Cells(iRow, "C").Value = myStr
End If
Next iRow
End With

End Sub
'from Tom Ogilvy
Function Split97(sStr As Variant, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

If you're using xl2k or higher, you could make a couple of changes:

Drop the Split97 function in its entirety.
And change the reference to Split97, to just plain old Split and Replace
"application.substitute" with Replace:
This
myCurPos = Split97(Application.Substitute _
becomes
myCurPos = Split(Replace _

(both of these were added in xl2k.)


I also guessed that you used row 1 for headers.


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

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there. But change the cell addresses to save and the names of
the worksheets.

Now go back to excel.
click on Tools|macro|macros...
click on the macro name (testme--rename it to something nicer)
and then click run.

=======
Another way to run this kind of thing is to show the Forms toolbar. Drag a
button from that toolbar to the worksheet (in a nice out of the way spot--Cell
C1???)

Assign it this macro.

Do it for each sheet you need. (Or set up one sheet and use that sheet as a
template for the other sheets--you can't be too far into it. Pitchers and
catchers have a couple of weeks before they report.)

Then when you make a change to the data, click the button.

 

[Ed]

Thanks so much for your help, this works perfectly. One last question
(please forgive me for being so new to macros): can I make this run
automatically after a player's info is entered into columns A and B or
do I need to "Alt-F8" every time I want each sheet to update?

 

[Dave Peterson]

You could make it run automatically by triggering it to a worksheet change.

But I wouldn't. I think I would just run it whenever I made a change. Then I
could correct typos, sort it the way I want, add/delete rows--all that data
entry stuff.

But I would think about dropping that button from the forms toolbar onto the
worksheet--I find that easier than alt-F8. (That was the last portion of my
original post.)