Google Spreadsheets

[doulos]

I am certain that there are some statistically minded hockey fans out there that know how to do this so figured I would ask here first.

I am using Google Spreadsheets to track Stats from various leagues and I currently have a spreadsheet with a list of names of players and stats.

On a second sheet I want to create 'teams' of players that would pull their stats from the first Sheet.

For example on Sheet 2 I might have ...

Nugent-Hopkins, Ryan
Eberle, Jordan
Atkinson, Cam

On Sheet one I have all of their stats and I want to pull them into the second sheet based on the name.

How would I do this? I am sure it's easy enough but I hardly ever use spreadsheets in my current job.

[fantasyhockeygeek]

vlookup is your hookup!

=vlookup(cell containing the name of the player your want to look up the stats for, range of cells you want to search in with the player name being the first column, number of the column you want to be outputted, FALSE)

For example, you have the players on your "stat" sheet, called "stats".

• Their names are in column A from rows 2 through 100.
• GP is column B, P is column C, PIM is column D

On your "lookup sheet", you'd put the player name in column A. Say you're on row 2. For column B (where you want to output GP for the player in question) you'd input "=vlookup(A2, 'Stats'!$A$2:$D$100, 2, false)".

For column C, where you want P, it would be "=vlookup(A2, 'Stats'!$A$2:$D$100, 3, false)"
Hope that makes sense? Probably doesn't.

[doulos]

How did I know you would be the one to answer?!

I am going to go try this right now and see if I can get it to work. Will report back. Thanks!

[ericdaoust]

It may take some practice getting used to. I've been using vlookup for years and still stumble with it at times.

[doulos]

I may have hit a snag in that since I am using importxml to grab the data it doesn't have the correct values in my 'stats' page.

[DerekReese]

I may have hit a snag in that since I am using importxml to grab the data it doesn't have the correct values in my 'stats' page.

You want to make sure all your "data" is in number form and not text form. I also find that if the names dont match exactly it wont work either.

[fantasyhockeygeek]

Sure, but that shouldn't impact your ability to figure out how to look them up. Just start with dummy stats to begin with, get the lookup right, then get the stats in there.

[doulos]

The syntax for the vlookup seems pretty straightforward but I think it won't work the way I have grabbed my data. I am using importxml to snag it from the AHL website for testing purposes, and so the value of the cells in the Stats sheet are not names, but are something else.

[ericdaoust]

The syntax for the vlookup seems pretty straightforward but I think it won't work the way I have grabbed my data. I am using importxml to snag it from the AHL website for testing purposes, and so the value of the cells in the Stats sheet are not names, but are something else.

But isn't the player name included somewhere in the data that you've fetched?

[doulos]

But isn't the player name included somewhere in the data that you've fetched?

Well it 'displays' as Jordan Eberle, but when I click on the cell itself the value is =CONTINUE(A2, 3, 3) which I assume is because I am using =ImportXML to grab the data.

I never use spreadsheets so it's probably something silly...hahah.

[DerekReese]

Well it 'displays' as Jordan Eberle, but when I click on the cell itself the value is =CONTINUE(A2, 3, 3) which I assume is because I am using =ImportXML to grab the data.

I never use spreadsheets so it's probably something silly...hahah.

What you can try is add a new column and then copy the players names and paste it using "Paste Values". Then you will only get the players names. Then do your Vlookup on those names.

[doulos]

What you can try is add a new column and then copy the players names and paste it using "Paste Values". Then you will only get the players names. Then do your Vlookup on those names.

BAM! Yeah that worked great! Awesome stuff, thanks!

[doulos]

Just realized I can save my initial sheet as a CSV file and then I can do all sorts of stuff with it using some other software. You guys are awesome, this has solved some problems I was having very nicely!

[ericdaoust]

Just realized I can save my initial sheet as a CSV file and then I can do all sorts of stuff with it using some other software. You guys are awesome, this has solved some problems I was having very nicely!

Yeah csv is just a plain text file so it's very easy to use and tons of programs support it. You can even make fast edits in notepad.

[Sportfreunde]

Effin Google Sreadsheets doesn't work properly for me anymore. I can't edit what I upload and it shows up without the actual columns. Says something about browser not supported (tried with firefox and IE).