Formula help... Sorry for the long drawn out post. 1

[Kenja824]

I am attaching a file to help with matters... I believe I am looking for some form of Index/Match formula, but I am not sure.

In the file, there are two tabs. Yahoo and Formulas.

YAHOO tab = I will go into Yahoo football and find a box score for a game played. I will copy and paste the box score directly from yahoo website to this sheet. ... Important - Depending on what happens and which players perform well, the players names can fall on different rows of column A, and I think it is possible the stats could end up in different columns each time.

FORMULAS tab = Here I will have the formulas I want to search the box scores and return the specific stats I need.

This may sound like a straight up index/match formula, but here are my problems....
Depending on whether the player ran with the ball, caught the ball, or threw the ball, his name can appear multiple times.

As you will see the name "27 Kareem Hunt" appears in this case at row 11 & 29.
My formula tab will have cells looking for his yards but they need to find the correct cell whether it is his receiving yards or rushing yards.

Example: Cell J6 of the Formulas tab is looking for Hunts rushing yards in Yahoo tab. Cell N6 is looking for his receiving yards in the Yahoo tab. In this case, J6 would = YAHOO C11, and N6 would = YAHOO C29. Both ended up on "C" but there is no guarantee it will always be so.

How do you get a formula to return the correct cell if the name of the player can appear twice?
How do you get the formula to find the right category if the YDS category for receiving is in one row and the rushing yds is in another row and there is still yet more yds categories for passing, returns, and defense?

Important notes:
1) I am trying to not have to manipulate any info on the Yahoo sheet. This is strictly copied and pasted from a website. I will have to do this for multiple games each week.
2) I cannot be sure any particular name or stat will fall in any specific column or row from one box score to another.
3) I cannot be sure the players name will even be in a box score, so if it is not found, I need it to return "0"
4) One week I will be adding box scores for multiple games. The next week I will be adding more. I think all of the box scores in one week will be pasted on the same sheet below one another.

Im guessing it would need to look for the word Receiving (for example) and find the first cell named "Yds" in a row below the Receiving cell. Then do the same for the players name and do an index match using those two cells locations? I have no idea how to write that into a formula though. A standard Index/Match is deep enough for me to figure out. lol

If this is something better done in VBA, that is fine. I am not very good with that but it will give me more practice and learning opportunity.

Using Tables in this case I think is not an option if they would help because I would have to go into each box score and create a table every time.

Sorry for the long drawn out post. If this task is a very difficult one, it is not actually important. I am just looking to make it easier to keep track of while it is going on instead of having to read each box score and manually input their stats. If I get it done and it works easy enough I may consider using it for the regular season as well. lol

 

[SkipVought]

Hi,

Doing stats for a fantacy football league? I know that in the league my sons participate in, since each week its head to head, each participant pair calculate both team's points as a check on each other. But maybe this exersize is somewhat different.

First, I'd make a copy of each week's Yahoo & formula sheets to maintain a history.

Next, VBA for sure. 1) you'll need to store key parameters describing the structure of the Yahoo sheet like where does each section begin: these sections will correspond to the scoring sections in your Formula sheet. What you could do is define section ranges, for instance ...


[pre]
Range: Rush_KC
Kansas City. Rush Yds Avg Long TD FL
27 Kareem Hunt. 17 148 8.7 58 1 1
35 Charcandrick West 1 21 21 21 1 0
10 Tyreek Hill. 2 5 2.5 4 0 0
87 Travis Kelce 1 4 4 4 0 0
13 De'Anthony Thomas 1 4 4 4 0 0
11 Alex Smith. 5 3 0.6 7 0 0

Range: Rush_NE
New England. Rush Yds Avg Long TD FL
35 Mike Gillislee 15 45 3 16 3 0
28 James White. 10 38 3.8 10 0 0
15 Chris Hogan. 3 17 5.7 13 0 0
34 Rex Burkhead 3 15 5 14 0 0
33 Dion Lewis. 2 9 4.5 5 0 0
12 Tom Brady. 2 0 0 1 0 0
[/pre]

I think that once you had ranges like this defined, then the Formulas sheet would be a piece of cake.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Here's your workbook with
1) three ranges in Yahoo defined for passing, rushing, receiving and
2) in Formulas, formulas

So this leaves the VBA task of defining ranges for passing, rushing, receiving, kicking, etc.

What do you think?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

Hey Skip

Thanks for the help, and I see what you are doing here, but the problem is, when I copy a box score from YAHOO, the range will change according to the stats. One box score may have a total of only 4 players who rushed for yards, but the next games box score may have 10 different players who rush for yards. This not only changes the range for the rushers, but for where the receivers will fall and the defense and such. My goal is to go to yahoos box score, copy it and paste it with no formatting and the FORMULAS sheet to automatically find the correct info in the pasted box score.

It is for fantasy football but not the typical. For regular leagues we just use the free Yahoo or Espn leagues and let them track everything. What this is, when the playoffs start, we do a league where everyone picks a line up for round-1 out of the players who are playing in round-1. Everyone can pick the same players if it comes down to it, it doesnt matter. They will get the points the players they have in their lineup accumulate. The second round, everyone does the same. Then the next round up to the Superbowl. The one catch is that once you pick a player, you cannot use him again. So you need to be careful to try and save players who will be in the superbowl for last so you wont be without any starters.

So each round I usually go through every game and enter the stats from each boxscore. Pain in the but. I am trying to come up with a way to just copy and paste the boxscore and let formulas do the work. If it works well enough, I would consider seeing if others want to try doing this kind of league for an entire season. Or at least whatever games are left when I get this done. lol

Attached is the file I used in the past. I have learned a lot since then but the scoring tab I would fill out that manually and let the formulas add up the scoring. Copying and pasting boxscores is an idea I got because I got tired of manually entering it all in. lol Some of my greatest accomplishments have come from laziness. lol

 

[SkipVought]

...but the problem is, when I copy a box score from YAHOO, the range will change according to the stats.

Of course.

Sub YahooSetup()
'SkipVought 2017 SEP 9
'FIRST SHEET is always the Yahoo sheet
'copy Yahoo sheet for history after FIRST SHEET[b]
'Run this after pasting in new stats for the week[/b]

    Dim r As Range, rFound As Range, xl As Application, rng As Range
    
    Set xl = Application
    
    With Sheets(1)                                  'THIS IS THE YAHOO SHEET
        For Each r In [Sections]                    'Sections (passing, ruching, etx)
            Set rFound = .Columns(1).Find(r.Value)  'find each section on Yahoo sheet
            
            If Not rFound Is Nothing Then           'when found
                xl.DisplayAlerts = False            'turn off alerts
                                                    'define the range
                Set rng = rFound.End(xlDown).CurrentRegion
                                                    'add the range name
                ThisWorkbook.Names.Add _
                    Name:=Intersect(r.EntireRow, [rng]).Value, _
                    RefersTo:="=" & .Name & "!" & rFound.End(xlDown).CurrentRegion.Address(True, True)
                
                xl.DisplayAlerts = True             'turn on alerts
            End If
        Next
    End With
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Your weekly process should include:

1) copy the Formula sheet and rename

2) select all cells on the renamed sheet, COPY and Paste Special -- VALUES

3) copy the Yahoo sheet and rename

4) paste new Yahoo data for new week

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Here's another copy of the workbook with...

VBA procedure for naming ranges in Yahoo sheet
Factors sheet containing table of Sections and corresponding range names

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

Wow, thanks Skip.

I left for the weekend and came back to a bunch more help. lol Kind of makes a Monday start of good. )

psst... Now I just gotta look this stuff over when bosses arent looking. lol

 

[SkipVought]

I really enjoy coding. I have two sons that are about 50. They both participate in a FFL. One of my sons lives out of town. So he asked me to sit in for him at the draft in August. So I wrote an Excel application to aid me to mannage his prospective player list through the draft process. And I had some fun doing that for him for several seasons, tweeking the process and code a bit each year.

So this was/is fun! Retired now but trying to maintain my skills.

Alt+TAB flips to another screen in a flash.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

Something I am having a hard time wrapping my head around....

On the YAHOO sheet, currently I am showing one box score. It is the Chiefs vs Patriots. In a playoff round there will be several different box scores pasted. If I paste the second box score on the same sheet, I would have to go through the formulas and create new ranges I think. I guess the same if I pasted the second box score on another sheet. Then there is the third box score and fourth for the other games played in the same week. The players that are chosen by those who are in the pool might be from any team that is playing.

That is confusing enough to set up, but here is where it gets really confusing. The next week the teams that are playing will be playing different teams. So a formula looking for a player in one week will have to find a completely different box score the next because I cant place the same two teams together each week.

If I was to build this to work for a regular season pool, this would get really crazy with 32 teams playing 16 games and the next week the box scores would all be between two different teams again. I am sure there is either something I just dont understand about how it all works or some way to work with this and make it work for what I need. My biggest problem, other than lack of knowledge about coding, is time to spend on it. And the less one knows about something, the more time they need to do it. lol

...........................

I love working with excel and formulas for my personal stuff. Only recently have I had more opportunity to use it for work and I have delved deeper into it than the past. It hasnt helped that I havent had microsoft at home either.

Im slowly learning VBA code. Very slowly. lol

Im glad to hear you enjoy this stuff. I hate to think I am being a hassle with so many questions in recent months. You have been a huge help.

 

[SkipVought]

The logic that the current application uses on the Yahoo sheet assumes this:

There are 7 sections.

There is ONE table in each section with players from multiple teams. No blank lines within any section table.

So with those two assumptions, you could list all the data from all the teams for a particular week within the 7 sections.

If i'm off, plz post an example of what you mean.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

Also....
To simplify what I am trying to do.... I think my example may have been making it mire difficult than it needed to be.

On one tab, I would like to copy the box scores (as many as possibly 16 of them) and paste them into the excel sheet. Preferably all on one sheet. On a second sheet, I would have all of the rosters of players that people chose for their lineups. That second sheet would just search the first sheet for the right player and obtain the stats.

The one thing to remember however, is that the player may be on the boxscore in multiple places.

I can see how this will have to be done with new sheets added for each week. That would be fine. I just think placing every box score on a different sheet may become too much, and I dont know how hard it would be to make the code look for a particular player over an entire sheet filled with a bunch of different boxscores and obtain passing, rushing and receiving stats.

 

[Kenja824]

It might be best for me to create a better example of what I need. I will post it after lunch coming up....

Thanks )

 

[SkipVought]

Yes, plz post a more realistic example. Is it just the Yahoo sheet or more?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

This is a little different from the original but only because I realized the original was a little redundant. The more I worked into this, I feel this is the simplest setup, but you can change it if you want. lol

Two tabs. These are for one week's worth of games. To save time, I only added 6 boxscores all one above the other. And I just realized that when I selected players for the teams rosters, I only picked from three boxscores, but it will still get the point across I think.

BOXSCORES-1
As mentioned, this has the boxscores copied straight from Yahoo and pasted to this sheet. I put them below each other so all players names are on the same column. If used for regular season games, there would be as many as 16 box scores in one week.

ROSTERS-1
This has the lineups for each person who is playing in this. When they turn in a list of players for their week, I will simply get the players names and fill their rosters. The formulas would update and fill in all of their stats after I paste their boxscores after they play.

I can easily enough come up with formulas to add up their stats and give them point values. I dont want to bother you for that.

Every new week, I will copy these sheets and rename them BOXSCORES-2 and BOXSCORES-3 etc...

NOTE: Keep in mind that with new weeks, the people will choose new players to fill their rosters and the box scores will have different teams facing each other, so the teams boxscore cannot appear in the same location from week to week.

Please let me know if something doesnt make sense. Again, I do appreciate the help. This is a great learning experience for me.

 

[Kenja824]

One other thing I have been thinking about in the back of my mind and I thought I would toss it out there for you....

For defense, I would want to pick a team defense instead of a single player. So the formula would have to find the teams players stats and add them together. Not sure if that would be much more difficult or not.

 

[SkipVought]

Lets see the full layout on the Yahoo sheet and the corresponding Formula sheet.

I would think that the Formula sheet structure/layout would not change week to week.

Do you need a Games sheet that relates the teams playing each week?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

I suppose you are right. I would not need to copy and paste new sheets each week. I could always add new sheets and copy and paste just the values to them to keep records of each week. Just use these two sheets for doing the work every week.

So from one week to the next, the only things that would change would be that I would fill each lineup with entirely different players. For the BOXSCORE sheet, I would have to clear it and paste the new week of boxscores to it.

I really dont have anything more I can give you as I havent actually built it yet. In an earlier post I attached my old one from the past when I used to manually enter the info. This new formula based idea is more just trying to learn how I could even do it more than building it yet. What I attached a couple replies ago is the most developed one yet. Probably how I would set it up but I would just clean it up to look better. Add column turning the stats to total points and such.

 

[SkipVought]

okay, I see your BOXSCORE-1 & ROSTERS-1 sheets.

I think we can make this work in some way.

Some thoughts.

I'd like to keep one sheet for the entire season for each of those sheets, rather than a sheet per week. So the box scores would need to be labled week by week. Then the rosters would string across the sheet, labeled by week.

Maybe the box score gets pasted into a new sheet each week, but the code would add the scores to a proper table that would have all the extra columns necessary to calculate stats per team per week.

You ought to start a new thread for this effort, posting your new workbook/sheet structure. This will be definitely a different approch.


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

I will do that. I will explain in the thread what I am looking to do so if anyone reads it they understand what is going on.