Problem sorting projections by position in Excel

[usualsuspects]

Hopefully somebody in the know about the projection files at HQ can solve this problem for me.

I copy and paste the HQ projections into my own spreadsheet that runs my own pricing formula.

Then, to make things "easy", I change some players' positions so that for example all OF's are "7", and multi-position players are shown at their one most likely position.

This is when the problem starts.

There's something about the way HQ formats the position cells in their spreadsheet which I can't duplicate.

The changed positions all move to the right side of the column from the left. I can select the column and chenge the alignment under cell format so everything lines up.

But, I want to sort by position for my draft worksheet. And when I do that sort, the lines where the position was changed all sort out on the top, and the lines where the HQ position was unchanged sort out beneath them.

I tried copying the column and pasting just values, that didn't help, nor did pasting just formats.

What voodoo trick is it you're playing on me?

[GoldenEagle]

Yes, there's an Excel error that's generated when the spreadsheet sees a number that it "thinks" is formatted as text. Text and numbers don't sort in the same hierarchy. Here's a quick way to fix it.

First, go to an empty cell and input the number 1.
Then, with that cell highlighted, click on Edit Copy (or CTL+C).
Next, highlight the entire position range (probably all the cells in Column B, edited and original) and then click on Edit PasteSpecial.
In the PasteSpecial dialogue box, you now have to look under Operation, and click on the "radio button" that says Multiply and then click on OK.
Voila'

Essentially, you're just multiplying all the cell contents in the range by the number 1, which doesn't change the value of the contents, but Excel now "thinks" that the product of the multiplication formula must be a number, and therefore stores all the "products" as a number, not as text. It's kind of a funny work-around way to do it, but it's fast and it works.

[cwhenning]

I have encountered this problem also. I believe I have a simpler solution. Before changing any of the POS values:

Highlight the entire Pos column (click on the B).
Right click in the highlighted area.
Select 'Format Cells'.
Select 'Text' as the format.

Away you go.

[MattC@HQ]

I have encountered this problem also. I believe I have a simpler solution. Before changing any of the POS values:

Highlight the entire Pos column (click on the B).
Right click in the highlighted area.
Select 'Format Cells'.
Select 'Text' as the format.

Away you go.

General note: This will work properly because all of the positions are a single digit. However, if you try to sort numbers formatted as text, Excel uses alphabetical rules, so it will sort by the first digit, then second digit, etc. Using this method, Excel will sort the series (1,2,3,12,18,24,26,30) as (1,12,18,2,24,26,3,30)

I would suggest the format change noted above, but change the format to "number" with no decimal places.

[Yardsmen GM]

I use autofilter, but keep the positions as text. In autofilter, I choose "contains" 2 (or whichever pos wanted) and you get all guys eligible at catcher.

The tricky part is OF, so I use Find and Replace. I find all 7s and change them to the letter O, then change the 8s and the 9s.

Voila.

[Tom_T]

If you use the Custom Draft Guide to generate your league's values, each player's primary position is listed separately. You can then copy-and-paste the entire table into Excel. Saves you the trouble of changing '789' to '7', etc.

[cwhenning]

General note: This will work properly because all of the positions are a single digit. However, if you try to sort numbers formatted as text, Excel uses alphabetical rules, so it will sort by the first digit, then second digit, etc. Using this method, Excel will sort the series (1,2,3,12,18,24,26,30) as (1,12,18,2,24,26,3,30)

I would suggest the format change noted above, but change the format to "number" with no decimal places.

I get the same sort results no matter if it's formatted text or number. Always comes up (1,12,18,2,24,26,3,30)

Things that make ya go Hmmmm.

[Tom_T]

I get the same sort results no matter if it's formatted text or number. Always comes up (1,12,18,2,24,26,3,30)

Here's the deal. When you highlight the column and select "Number" as the format, Excel still keeps the number formatted as text, so the sort is unaffected. Very annoying.

There are two ways to go (though I'm not sure what versions of Excel these work under):

1. When you sort the data, do you get a "Sort Warning" that says, "The following sort key may not sort as expected because it contains some numbers formatted as text"? If so, you should pick the option "sort anything that looks like a number, as a number."
2. If you want to truly convert the data in the cells to numbers: highlight the column and select Format, Number, as before. Next, highlight all of the values in the column, from top to bottom, excluding the heading. If you then look at the first cell, you'll see a little box to the left that has a diamond with an exclamation point inside. Click it. You'll see an option that says "convert to number." Selecting it will convert the entire column to a true number format, removing the text formatting.

[usualsuspects]

Thanks to all for the advice.

I like Eagle's suggestion, I had tried something similar with incorrect execution (i.e. I ended up with 49s, 36's, 25's, etc).

Ultimately it was less trouble to just type in all the right numbers, but I'm saving that fix for next year.

Meanwhile, HQ, how about just making the spreadsheet numbers format as actual numbers?

[Michael@HQ]

Meanwhile, HQ, how about just making the spreadsheet numbers format as actual numbers?

I'd prefer they keep the position in text format myself, mostly for consistency's sake. They aren't really numbers but rather position codes. The filter explained in Yardsmen GM's post works if they are in text format.

[Dish]

Yes, there's an Excel error that's generated when the spreadsheet sees a number that it "thinks" is formatted as text. Text and numbers don't sort in the same hierarchy. Here's a quick way to fix it...

Wow, am I going to be cool at the water cooler this week! I've always done the more tedious Select Column then Find/Replace a 1 with a 1, a 2 with a 2, etc. This will save at least 5 seconds! Somewhere, this adds up!

[timg]

When I do a sort on positions, Excel asks me if I'd like to "sort anything that looks like a number as a number". It then sorts in this order: 1, 2, 3, 23, 45, etc.

[KERL]

HATE the new data download. Some of us have long-standing formats developed over years where we could download the latest and it would update a keeper league date. Cannot do that now and it would take about 40 hours for me to reformat my spreadsheet.

Some Specific issues
Not all of the fields are in the new format and the order is not the same
Names are combined (first and last) when they were separate in the old system
Player IDs (which were much easier to do lookups on) are no longer there
Age is missing
Fields are not in the same order (MM Code, MM, $value ...)

I use the projections regularly during the year, The changes that were made were significant enough so that I (a subscriber since the paper format) will probably not be around next year unless these issues can be changed, There are other sites that can give many of the analysis this site does so it can be relaced.

Let me know if there is a way to correct the above issues

[KERL]

Added to above - cannot recreate my data sheets as key field is missing

[RAY@HQ]

This has been mentioned in several places, but bumping an 18-year old thread (seriously, this thread can VOTE) is a new way to raise the issue.

The CSVs will not go back to exactly how they were before, but we're going to make them a LOT more usable, and quickly.