Excel Question

[Ben E Lou]

I want to copy the following down a column:

=VLOOKUP('Draft Order'!D226,'Cities and Teams'!A2:B33,2)

I want it to reference D226, D227, D228, D229, and so on, but I want the A2:B33 reference to remain constant. How do I do that?

[wade moore]

Originally Posted by SkyDog

I want to copy the following down a column:

=VLOOKUP('Draft Order'!D226,'Cities and Teams'!A2:B33,2)

I want it to reference D226, D227, D228, D229, and so on, but I want the A2:B33 reference to remain constant. How do I do that?

=VLOOKUP('Draft Order'!D226,'Cities and Teams'!$A$2:$B$33,2)

[sterlingice]

If one of the Excel gurus isn't awake, I think I can help. If I remember right, you put a "$" in front of values you want to remain constant when you cut and paste. But you'd have to look into Excel help for the syntax.

Edit: looks like Wade got it

SI

[Ben E Lou]

Word. Thanks. That was the final piece. I may have a little Interrogator-based Excel app to share in a few minutes that will give you a draft order for FOF2K7 leagues.

[wade moore]

Originally Posted by sterlingice

If one of the Excel gurus isn't awake, I think I can help. If I remember right, you put a "$" in front of values you want to remain constant when you cut and paste. But you'd have to look into Excel help for the syntax.

Edit: looks like Wade got it

SI

FWIW you got the rule right, i explained the details to SD in IM but just pasted the formula quickly here.

[Ironhead]

Originally Posted by SkyDog

I want to copy the following down a column:

=VLOOKUP('Draft Order'!D226,'Cities and Teams'!A2:B33,2)

I want it to reference D226, D227, D228, D229, and so on, but I want the A2:B33 reference to remain constant. How do I do that?

And if you don't want to bother putting the number signs in yourself you could just highlight the portion of the formula you want to stay constant and hit F4. That will add the number signs automatically.

[Ben E Lou]

Next question:

I want to semi-automate the process of getting the worst performances of the week. Extender spits out a large csv with the stats and point values of every player who participated in the week's games. What I'd like to do is this (for several different categories, but let's take passing):

1. Search the column that has the total passing point in it for the worst 3 values.
2. Display the name, team, and passing stats columns for the rows containing those worst 5 values.

In other words, if -6, -9.4 and -4 are the worst three passing scores, I want to display the Name, Team, Passing Points, Att, Cmp, Yds, TDs and INTs for the rows containing -6, -9.4 and -4.

[gottimd]

Originally Posted by SkyDog

Next question:

I want to semi-automate the process of getting the worst performances of the week. Extender spits out a large csv with the stats and point values of every player who participated in the week's games. What I'd like to do is this (for several different categories, but let's take passing):

1. Search the column that has the total passing point in it for the worst 3 values.
2. Display the name, team, and passing stats columns for the rows containing those worst 5 values.

In other words, if -6, -9.4 and -4 are the worst three passing scores, I want to display the Name, Team, Passing Points, Att, Cmp, Yds, TDs and INTs for the rows containing -6, -9.4 and -4.

#1 Use the Min() formula I think. Or Small().
#2 Copy the Name, Team etc columns over to the right and do a vlookup?

[Ben E Lou]

Heh. I realized I have differing numbers in that recent post. A VLOOKUP on the MIN value would give me the info on the worst player, but I'd like to have the flexibility to get the worst or best x number of players. I'll look into the SMALL function. (I'm essentially Excel-ignorant, but I'm a relatively fast learner. I'd love to dig into this sort of stuff more.)

[Ben E Lou]

Yup, SMALL is the function I need, but now I'm running into another issue. The data is layed out in Extender as such:

Player	Position	Team	Exp	Pass	Rush	Recv	Block	MiscOff
Gus Oliver	QB	Coney Island	13	-15.89	0	0	0	-2.5
Jimmie Norris	QB	Westchester	3	-9.485	0	0	0	0
Lincoln Wallman	QB	Myrtle Beach	7	-6.68	1.17	0	0	0
Roman Keyes	QB	Shreveport	7	-3.715	6.82	0	0	0

It appears that VLOOKUP is limited in that it can only look up in the first column in an array, unless I'm really missing something. In other words, if I do:

=VLOOKUP(SMALL(foflfoot_SeasonAwardsWeek.csv!$E:$E,1),foflfoot_SeasonAwardsWeek.csv!$E$1:$I$2954,5)

It returns -2.5, in the fifth column over. Since all of the stats are out to the right, I can return those. However, for this to be worth my time, I need to be able to return "Gus Oliver" and "Coney Island," based on that -15.89 value. Thoughts on how to do that?

[headtrauma]

Originally Posted by SkyDog

Yup, SMALL is the function I need, but now I'm running into another issue. The data is layed out in Extender as such:



It appears that VLOOKUP is limited in that it can only look up in the first column in an array, unless I'm really missing something. In other words, if I do:

=VLOOKUP(SMALL(foflfoot_SeasonAwardsWeek.csv!$E:$E,1),foflfoot_SeasonAwardsWeek.csv!$E$1:$I$2954,5)

It returns -2.5, in the fifth column over. Since all of the stats are out to the right, I can return those. However, for this to be worth my time, I need to be able to return "Gus Oliver" and "Coney Island," based on that -15.89 value. Thoughts on how to do that?

I can't think offhand how to do this exactly, but I think a combination of INDEX and MATCH should be able to help.

Try this: http://www.ozgrid.com/Excel/left-lookup.htm

[Ben E Lou]

Good call. MATCH and INDEX did the trick, and I've got another little auto-report now.

[Ben E Lou]

Heh. This is like a dynasty thread. A little more help...

Player	Position	Team	Exp	Pass	Rush
Devin Kaesviharn	LDT	Chesapeake	5	0	0
James Ferguson	LCB	Westchester	4	0	0
Terrance Johnston	SS	Davis	11	0	0
Richie Shaw	WLB	Chesapeake	5	0	0
Brandon Sedor	SS	Madison	9	0	0
Brady Reilly	LCB	Shreveport	1	0	0
Clifton Taylor	LCB	New Jersey	8	0	0

I'd like to copy values from certain columns if the value of "Position" is C, LG, RG, LT or RT. If not, then I want nothing copied. I can see how I could use IF statements to return blank rows for positions that are not offensive linemen, but is there a way to return only o-line rows, not knowing how many there will be?

[SteveMax58]

You need to do an If with an OR.

IF(OR($B:$B="C",$B:$B="LT",$B:$B="LG",$B:$B="RG",$B:$B="RT"),copy something,copy nothing)

[Ben E Lou]

Originally Posted by SteveMax58

You need to do an If with an OR.

IF(OR($B:$B="C",$B:$B="LT",$B:$B="LG",$B:$B="RG",$B:$B="RT"),copy something,copy nothing)

I'm not following you with the "copy something", "copy nothing" stuff there. "COPY" isn't a function, is it?

[SteveMax58]

Originally Posted by SkyDog

I want to copy the following down a column:

=VLOOKUP('Draft Order'!D226,'Cities and Teams'!A2:B33,2)

I want it to reference D226, D227, D228, D229, and so on, but I want the A2:B33 reference to remain constant. How do I do that?

Just a note on the VLOOKUP. If the column being looked up is NOT always or by default sorted in ascending order...you may want to adda ",FALSE" to the end of your VLOOKUP formula. So it might look like this:

=VLOOKUP('Draft Order'!D226,'Cities and Teams'!$A$2:$B$33,2,FALSE)

[SteveMax58]

Originally Posted by SkyDog

I'm not following you with the "copy something", "copy nothing" stuff there. "COPY" isn't a function, is it?

No no...I didnt realize you needed help with that part. I had thought you had the copy part handled.

Give me a sec to read the rest of the thread to help with that.

[Ben E Lou]

Actually, I think I'm following you here. (Don't worry about the rest of the thread. Everything else I've solved.)

=IF(OR([foflfoot_SeasonAwardsWeek.csv]foflfoot_SeasonAwardsWeek!$B$2="LG",[foflfoot_SeasonAwardsWeek.csv]foflfoot_SeasonAwardsWeek!$B$2="C",[foflfoot_SeasonAwardsWeek.csv]foflfoot_SeasonAwardsWeek!$B$2="RG",[foflfoot_SeasonAwardsWeek.csv]foflfoot_SeasonAwardsWeek!$B$2="LT",[foflfoot_SeasonAwardsWeek.csv]foflfoot_SeasonAwardsWeek!$B$2="RT"),[foflfoot_SeasonAwardsWeek.csv]foflfoot_SeasonAwardsWeek!$A$2,"")

[Ben E Lou]

Yeah...that's what I thought it would do. It's returning blank rows where it's not a center, guard or tackle. Any way to auto-delete the blank rows?

[Ben E Lou]

(In other words, I've got my header row, then 153 blank rows, then a correct row containing info on LG Teddy Lefowitz, then 9 blanks rows, then a correct row containing info on RG Jeff Turnbull, etc. Everything I need is now populated in the table, but it's, uh, rather spread out.

[SteveMax58]

Yeah...that's one of the things that doesn't seem to have a clean work-around in Excel. The problem I see, with one of my ideas is that the position isnt unique...but seems to be the necessary key.

I'm guessing that you'll need to create a sort of "scratch area" in the file to store the positions with an array...bring the name along for "uniqueness"...then do the "real" data table you want based off of the "scratch area". Let me know if that makes no sense.

A couple of quick questions:
1) Are you trying to format data from 1 worksheet to the next(within the same file)...or consolidating multiple file exports to 1? Just a basic idea is what i'm trying to get.
2) Are you expecting to replace/overwrite the source file periodically?(i.e. automatically exported type of thing).
3) If you want it done quickly...you can email my screen name AT aol dot com.

[Ben E Lou]

Originally Posted by SteveMax58

Yeah...that's one of the things that doesn't seem to have a clean work-around in Excel. The problem I see, with one of my ideas is that the position isnt unique...but seems to be the necessary key.

I'm guessing that you'll need to create a sort of "scratch area" in the file to store the positions...create

A couple of quick questions:
1) Are you trying to format data from 1 worksheet to the next(within the same file)...or consolidating multiple file exports to 1? Just a basic idea is what i'm trying to get.
2) Are you expecting to replace/overwrite the source file periodically?(i.e. automatically exported type of thing).
3) If you want it done quickly...you can email my screen name AT aol dot com.

1. Neither, really. Stelmack's Extender utility for FOF2K7 spits out a csv with raw stats for every player in the game's database, with 77 data columns, most of which are irrelevant for any one given position group. (A LDT has a bunch of zeros in all of the rushing, passing, receiving, and blocking columns, for example.) I'm just pulling from one csv per worksheet, essentially creating some mini-reports from the data: pulling the relevant data for each position, and doing a few calculations. For the linemen, per the example above, I'm just pulling in these:

NAME

POS

Team

BlockPts

KRB

Pnck

Sks

PsPlys

RunPlys

and then calculating total plays, SkPct, and a point value for each player.

2. Yes. The source data file will be overwritten every time there's a sim.

3. Thanks, but I don't want to pawn this off on someone else, but just get help at critical points where I get stuck. I'm pretty Excel-ignorant, but I'm learning a ton in this process. It's looking like the end result of this will be something I can share with the community, and stats geeks like me should really enjoy it.

[SteveMax58]

I think I have a better idea of what you're trying to do now.

Have you considered using some macros w/ associated buttons(named by position type) to hide/unhide unwanted/wanted columns? This would seem to be the easiest way...if you are comfortable with macros that is. But this is the type of macro that you could record (i.e. no programming or anything).

In regards to your first answer...so are you doing 1 worksheet per position type or something like that? So this might be something you'd repeat for other worksheets/position types?

I have done something very similar to this before...but I have it on my work laptop(which is at work ATM) and the formula was pretty substantially complicated as I recall. I think I can recreate this somewhat easily...if I can I'll post the solution

[Ben E Lou]

Originally Posted by SteveMax58

Have you considered using some macros w/ associated buttons(named by position type) to hide/unhide unwanted/wanted columns? This would seem to be the easiest way...if you are comfortable with macros that is. But this is the type of macro that you could record (i.e. no programming or anything).

I was just playing with Macros. I think they'll take care of that issue.

In regards to your first answer...so are you doing 1 worksheet per position type or something like that? So this might be something you'd repeat for other worksheets/position types?

One worksheet per stats type (passing, rushing, receiving, blocking, defense, special teams). I can just use filters if I want to look at, say, the best receiving TEs or something like that.

I have done something very similar to this before...but I have it on my work laptop(which is at work ATM) and the formula was pretty substantially complicated as I recall. I think I can recreate this somewhat easily...if I can I'll post the solution

Cool! Thanks!

[SteveMax58]

OK...this assumes that you use the previous formula in column "A" to get the desired players...and that you use Row "1" as a header row...so that your first "import" cell is "A2".

Column "A" will then have quite a lot of blanks between these player names...this is expected. You will then put the following formula in Cell "B2". After you paste it(or type it)...you will make it an Array Formula by pressing Ctrl+Shift+Enter(starting at Ctrl...hold them down). You can then drag it down to the 3000(or desired) number of Rows.

=IF(ROW()>ROWS($A$2:$A$3000)-COUNTBLANK($A$2:$A$3000),"",INDIRECT(ADDRESS(SMALL((IF($A$2:$A$3000<>"",ROW($A$2:$A$3000),ROW()+ROWS($A$2:$A$3000))),ROW()-ROW($B$2:$B$3000)+1),COLUMN($A$2:$A$3000),4)))

After you enter that formula in Cell "B2"...you'll want to hide Column "A" permanently. This is basically just going to act as an import for you...and column "B" will be your filter for blanks. Now if you want to add a filter to your header columns, be sure NOT to include Column "A" in your filter(this will hose it up)...and realize that you cannot SORT ANY of the columns (sorting will hose this up as well). if this sounds like a potential disaster to remeber for yourself(or other users) you may want to consider not using the filters, and telling others not to put them in either.

Now to counter act the sorting inability...you'll want to add some buttons with macros which will sort your SOURCE data(i.e. your export) by the desired columns. So for example...in your OL worksheet, you'd have a series of buttons at the top which will sort your SOURCE data by the desired stats/vitals, etc. This setup would require your SOURCE data to be opened when you run these macros, however. One possible option is to create a worksheet tab which acts as your SOURCE data...maybe even add a macro button which automatically copies and pastes the export file's worksheet(with all players and data) into a SOURCE worksheet in your analysis workbook/file.

I have tested the formula on some other data with blanks between information and it has worked fine. I assumed your playerexport files will have no more than 3000 players...so if you think that might be too low, feel free to adjust that number up, just be sure to adjust all instances of the 3000 in the formula.

Let me know if you have questions or need any help with it...or want to change something up that I didnt account for.