Can anyone help with some Excel stuff?

[jumpman8288]

Alright, well I've been trying to steer away from asking the Cafe for help this year (a little experimentation on my part), but this doesn't directly correlate to fantasy baseball, so it doesn't count .

I'm trying to do averages of different sites' rankings in Excel, and I can't seem to find a way around manually typing in each player's rank from the different sites after the first one. I guess that's pretty confusing wording...well, after I copied & pasted the names and ranks from one site, I can't just copy the ranks from the next site because the names are in a different order. Is there any way I could get around manually typing this? It's quite tedious to find and type 300 ranks about 5 times.

Also...once I have all the ranks in and am finding the averages, is there any way I could copy the formula for the first player's ranks to fit each subsequent player?

[SHOCKandAWE]

You can use ACCESS and enter the name once and thenyou willonly have to enter the ranking for each list

[jumpman8288]

You can use ACCESS and enter the name once and thenyou willonly have to enter the ranking for each list

I wish I knew what you were talking about. Is ACCESS a different program? That sounds pretty sweet though, thanks

[SHOCKandAWE]

If you dont have ACCESS then forget it. It is too expensive to buy just for this

[jumpman8288]

Shoot

Any cheaper suggestions?

[SHOCKandAWE]

Sorry :(

[slomo007]

Also...once I have all the ranks in and am finding the averages, is there any way I could copy the formula for the first player's ranks to fit each subsequent player?

I can help you with this part:

Highlight the cell that has the formula you want to copy. Move the cursor to the bottom right corner of the cell, there should be a little box there where your cursor will turn to a smaller plus sign. Just left click that box and drag it through the cells you want to copy it to.

[Tavish]

You can use ACCESS and enter the name once and thenyou willonly have to enter the ranking for each list

I wish I knew what you were talking about. Is ACCESS a different program? That sounds pretty sweet though, thanks

Access would definitely be the way to go, its a basic MS database program that is usually included in the Office Suite. As long as you set up the player names in the same fashion on each Excel worksheet you can easily combine everything with a single Append query.

FirebirdSQL is a decent free RMDB that is available for download and is Windows compatible if you don't have (or want to purchase) Access.

[shpuck]

Is there any way I could get around manually typing this? It's quite tedious to find and type 300 ranks about 5 times.

Yep. There's a trick that I use, and it incorporates the web browser, Word and Excel.

Go to the source web page and copy the data you're interested in. Paste it into Excel. If it's already in tabbed format, each column of data will be in a different cell column. If it isn't you have to put it into tabbed format in Word.

Paste the text into Word. Use the "Replace" feature under the "Edit" menu. If your data was not in tabbed format already, it is probably separated by spaces.
In the Replace field, press the 'spacebar' once, twice, etc. to enter a space consistent with the gaps in your pasted data (nothing will appear, but the cursor will move)
In the Replace with, press "Ctrl-T". This puts in a tab.
You may need to put a carriage return as well, this is "Ctrl-P" for paragraph.
Press "Enter" or "Replace" and all your spaces will be replaced by tabs. Test this out a bit so you get the hang of it.

Now past all the data into Excel. Chances are, the name will be in one cell. Copy this column, paste it into word, and make another tab between the names.

Copy these newly created two columns back into Excel, and the name should be split between two cells. It is now sortable.

Repeat this for other sites rankings, but in a different spreadsheet for each site.

To get the names in the same order (roughly-some sites don't list all the same players) simply alphabetize by one of the columns, I use last name. MAKE SURE YOU SELECT THE ENTIRE ROW OF PLAYER'S NAME AND RANKING otherwise you're rankings will be screwed up.

Once you have one spreadsheet organized by last name, paste from the other into your "master" (the first sorted or another file altogether). Then you can carry out your averages for the placement of all the players, or whatever other manipulation you'd like to do.


I didn't quite understand the last part of your post, so I won't comment on it.