Spreadsheet (Fantrax + FHG)

[hydrobomb]

Hi,

I've created a spreadsheet using data pulled from Fantrax. I'm able to gather information from the site for the last 5 years and compile stats into one sheet. I'd like to add the Dobber projections into this spreadsheet, but the names of the players do not match on both spreadsheets. Here's an example:

Fantrax: Crosby, Sidney - PIT
Dobber: Sidney Crosby

I'm not able to pull the stats from FHG spreadsheets because the names don't match. Does anyone have a solution?

[evans334]

There's got to be some sort of excel function you could use....I'm not a huge excel guy.

This might help??

http://www.excel-university.com/perf...okup-in-excel/

[MarmotKing]

In excel's data tab, use the 'text to colum' feature and follow the instructions. That will seperate Crosby, Sidney - PIT into | Crosby | Sidney | - | PIT |.

Then you can use the formula : =C5&" "&B5 , where B5 is the cell where Sidney is and C5 is the cell where Crosby is. The middle part of the formula is needed to add a space between the two names.

After that you can copy the cells, right click, paste special, values. That way you will end up with Sidney Crosby in the cell, and not the formula.

After that, just get rid of the extra colums you don't need.

Hope this helps.

[Pengwin7]

I've used the Text-to-Column feature that Marmot writes above.
You could split up a name, then same-sort by Excels, then do data entry (probably not prefered, but I find I gain knowledge through my fingers when I do data entry - sounds weird, I know, it's a "type" of us out there).

Open both up Excel files on your screen(s).
I'd take Dobber's Excel.
Add a column to the right of player name.
Then highlight both columns (the player name-left, empty column-right) and do the text-to-columns to split Dobber's names into a FirstName, LastName.

Then you could just sort each same way (by last name, then first name) and do side-by-side data entry... (long way, yes I know).
Funny thing about data entry... sometimes you learn things just be typing them out.
You catch something here or there that you wouldn't have noticed by just skimming but would notice by physically typing it.

[filoufelin]

Excel has a function called VLookup that enables you to automatically get info from one table into another. As long as the names are correct and in the first column of the table, you can then do Vlookup searches for each info you want...very easy once set-up and saves all the time of manually looking things up. I just discovered it this week preparing for a draft Lots of teaching help online also to use this function.

[ericdaoust]

Excel has a function called VLookup that enables you to automatically get info from one table into another. As long as the names are correct and in the first column of the table, you can then do Vlookup searches for each info you want...very easy once set-up and saves all the time of manually looking things up. I just discovered it this week preparing for a draft Lots of teaching help online also to use this function.

I believe with vlookup the target "table" has to be sorted alphabetically.

[MarmotKing]

I believe with vlookup the target "table" has to be sorted alphabetically.

If you are looking for an exact match in Microsoft Excel, the first column of a lookup table does not have to be sorted to use the VLOOKUP and HLOOKUP functions. To look for an exact match, specify the fourth argument of VLOOKUP or HLOOKUP as FALSE. If you omit the fourth argument, or specify the argument as TRUE, you must sort the first column of the table. The OFFSET function described earlier also works in Microsoft Excel, but is not necessary.
(https://support.microsoft.com/en-us/kb/181212)

I had to look it up, because I was getting worried that my spreadsheet was feeding me wrong information!

I did find a nice little trick a couple of weeks ago to find data when you have extra info in the cell (i.e. Sidney Crosby, C , PIT).

What I use is the same vlookup formula [ =VLOOKUP(E4&"*";'Player info'!$C:$G;4;FALSE) ], and add this [ &"*" ] after E4. It tells the formula to look for the information in E4 (i.e. Sidney Crosby) but also anything that follows (i.e. , C , PIT). So in the end it will search for Sydney Crosby and anything that follows in the cell.

That way I don't have to clean up the names when I gather information from sites that don't offer clean names.

[hydrobomb]

Thank you so much. I'll definitely be able to separate the information using TEXT TO COLUMN or VLOOKUP.

[ericdaoust]

One problem that I've run into in the past is when the same person has different name spellings from one source to another. "Mike" vs "Michael", "Alexei" vs "Aleksey", etc. Using IF statements with your VLOOKUP commands you can make it post a blank value if an exact match is not found. Then it's easy to spot the problematic names and fix them manually so they match across the board.

[hydrobomb]

I'll give it a shot. I'll keep playing with it. I'm bound to figure it out sooner or later.

[MarmotKing]

One problem that I've run into in the past is when the same person has different name spellings from one source to another. "Mike" vs "Michael", "Alexei" vs "Aleksey", etc. Using IF statements with your VLOOKUP commands you can make it post a blank value if an exact match is not found. Then it's easy to spot the problematic names and fix them manually so they match across the board.

That's the one thing I hate the most. Why do they have different spelling!?

My vlookup formula usually returns a #N/A when it's not found, so I know to look for an error somewhere.