| RotoGuru Hockey Forum View the Forum Registry |
| |
| 0 |
Subject: Excel Question for Richard Posted by: Rick - [26726281] Thu, Oct 12, 21:06 Richard: I have learned a lot over the past year or so from you and your help with macros (From baseball I think). In our rotisserie draft I am one "small" problem away from full automation. (Until I think of something else to put in). The question I have is - On the Standings reconciliation page where I show the point totals for the various categories and their roto points (where 1st gets 7, 2nd gets 6... 7th gets 1), I use the rank function, but when two or more teams are tied in a category is there a command or another way that I can tell the spreadsheet to average these totals? Thanking you in advance! Rick | ||||||
| 1 | Richard ID: 58435323 Thu, Oct 12, 22:12 |
I'm sure there is some convoluted (almost impossible to explain) way of doing this but, off the top of my head, I'm not familiar with one. Let me think about this one for a bit. I love an Excel challenge (especially if I have some time to work on it). To make sure I understand what your need, if two teams tie for 1st in a category you want to give them both 6.5 points ((7 + 6)/2)? Richard | |||||
| 2 | Rick ID: 26726281 Thu, Oct 12, 22:15 |
That is exactly what Im looking for. Im just glad you were able to understand what I was trying to say. I knew what I meant - was just hoping you could decipher my meaning. Thanks Again Rick | |||||
| 3 | Richard ID: 58435323 Thu, Oct 12, 23:28 |
Rick - I've sent you an example spreadsheet via e-mail. Hopefully you can follow what I did. I hope you don't have too many technical questions because I barely understand what I did to make it work. Thanks for the challenge. I've learned some new Excel tricks tonight. PS - if you ever find a clearer and simpler way to to this, please let me know. Richard | |||||
| 4 | Rick ID: 26726281 Thu, Oct 12, 23:40 |
Richard: I very much appreciate the time you have spent on this and will check it out immediately and let ya know. Thanks Again Rick | |||||
| 5 | Rick ID: 26726281 Thu, Oct 12, 23:54 |
Richard: I'm able to follow it - but like you I barely understand it. I guess this is one of those cases of who cares how it works as long as it works. I really appreciate the quick response to my long time problem. Seeing that your specialty is macros and downloading from external sources, I have another test for ya if you are up for the challenge. You will recognize this macro as you are the author (I believe it was for the smallworld baseball rosters from last season.) It is for our golf pool. If you go to this website at pgatour.com you will see these funky titleist logos on top of all names that use those balls. When I run the macro like this, I get rid of the image but it adds three spaces to each golfer that had this image. Now in order to get my whole spreadsheet to work I have to manually delete these 3 spaces. Any ideas? Sub Get_Golfers() ' ' Get_Golfers Macro ' Macro recorded 27/08/2000 by Webmaster ' ' Keyboard Shortcut: Ctrl+z ' Workbooks.Open Filename:="http://www.pgatour.com/stats/r_109.html" ActiveWindow.LargeScroll ToRight:=1 Range("F16:H265").Select Range("H17").Activate Selection.Copy Windows("NewGolf.xls").Activate Sheets("DOWNLOADS").Select Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("A:C").Select Columns("A:C").EntireColumn.AutoFit End Sub Thanks Rick | |||||
| 6 | Richard ID: 58435323 Thu, Oct 12, 23:58 |
I'll take a peak at it tommorrow, I'm going to make a hockey trade (Hull->Jagr) and then go to bed. Richard | |||||
| 7 | Rick ID: 26726281 Fri, Oct 13, 00:02 | No rush - goodnight | |||||
| 8 | Richard ID: 58435323 Fri, Oct 13, 09:27 |
Rick - try inserting the following code right before the End Sub statement: Application.CutCopyMode = False Range("A1").Select Cells.Find(What:="Player").Activate Selection.Sort Key1:=Range("C15"), Header:=xlYes Columns("D:D").EntireColumn.AutoFit This finds the cell containing "Player" (You could assume that it always appears in cell D14 but I wasn't sure so I search for it) and then sorts the block of data using column C (Player Rank) as the sort key using row 14 as header titles for the block of data. This magically moves all of those blanks lines associated with the Titlelist mark to the bottom of your list of player. You can do the same thing manually by selecting cell D14 and then going thru the sort command on the data menu (make sure to click the option button for header rows). | |||||
| 9 | Rick ID: 26726281 Fri, Oct 13, 13:01 |
Richard: No luck on this one. I'm not sure if I'm doing something wrong but all that happens when I run the macro now is that it re-sorts the players by first name. The eventual sort order is of no consequence to me but those three damn blanks are still there. Do you have any other ideas or should this work and its just me? Thanks Rick | |||||
| 10 | Richard ID: 58435323 Fri, Oct 13, 13:45 |
Rick - are you using Excel 97 or Excel 2000? I think it might make a difference. At least I get a different block of data back from the website when I run you golf macro in Excel 97 vs Excel 2000. This is normally the situation when Excel 97 and/or Excel 2000 is used to grab web pages. Each handles web pages and grabbing data from the web pages slightly different. I tested your code in Excel 97 and it brings back the block of data with the Player names in column D and the player rank in column C and it places a row of blank cells below each player who uses Titlelist golf balls. However in Excel 2000 I see that 3 spaces appear at the end of the player's name if they use Titlelist golf balls and there are no blank lines in the block of data. My solution works great in Excel 97 but fails to do the trick in Excel 2000. Back to the drawing board. Richard | |||||
| 11 | Rick ID: 26726281 Fri, Oct 13, 13:53 |
Richard: That's it! I am using excel 2000. and I thought that 2000 was new and improved. Go figure. I really appreciate the time and energy (not to mention brain neurons) that you are spending on this to try and help me out. By the way. I have fully incorporated your fix to all of my categories in the hockey spreadsheet and it works flawlessly. Now it takes less than a minute to update (a far cry from going through the newspaper player by player). Rick | |||||
| 12 | Richard ID: 58435323 Fri, Oct 13, 14:55 |
Rick - when Excel 2000 brings back those players who use Titlelists there are three extra characters on the end of the player's name, but you already knew that because that's the problem you asked for help solving. Upon closer examination, I've discovered the last character is a chr(160) (did you know that each character, both printable and non-printable have their own number?). The standard trim function refuses to remove this character, but we can test for the presence of such a character and remove it if it is found. The following code fragment will loop thru all of the player names, assuming that they start in row 6 of column C, and remove the last three characters from each player's name if that player uses a Titlelist: player_row = 6 While Range("C" & player_row).Value <> "" player = Range("C" & player_row).Value If Right(player, 1) = Chr(160) Then player = Left(player, Len(player) - 3) Range("C" & player_row).Value = player End If player_row = player_row + 1 Wend Give it a try. You can place this code just about the End Sub line. The only remaining concern is what do we do if the starting row moves around (maybe sometimes Excel brings the list back and it starts in row 4 or 2 or 5 or some such spot). Richard | |||||
| 13 | Rick ID: 26726281 Fri, Oct 13, 15:12 |
Richard: We all should bow down to the KING (King Richard - its got a ring to it). You are THE MAN. Thanks. But now that you have given me all of this extra time I can improve on my 355 ranking in hockey. Thanks a bundle, Rick | |||||
| |||||||
Post a reply to this message: (But first, how about checking out this sponsor?) | |||||||