Announcement

Collapse
No announcement yet.

Problem sorting projections by position in Excel

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem sorting projections by position in Excel

    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?
    Last edited by usualsuspects; 03-14-2006, 03:19 AM.
    http://youtu.be/YtpkrIS4Sig?hd=1

  • #2
    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.
    Last edited by GoldenEagle; 03-14-2006, 04:27 AM.
    "As often is the case, GE is right." -- Davitt@HQ

    Comment


    • #3
      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.
      Nothing left to do but smile, smile, smile

      Comment


      • #4
        Originally posted by 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.
        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.
        "Never make predictions, especially about the future." -- Casey Stengel

        Comment


        • #5
          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.
          "Give that fan a contract!" - Rex Barney

          He's throwing a 2 hit shutout and he's shaking me off, can you believe that sh**! Charlie, here comes the deuce; and when you speak of me, speak well. - Crash Davis

          Comment


          • #6
            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.

            Comment


            • #7
              Originally posted by mattyc33
              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.
              Nothing left to do but smile, smile, smile

              Comment


              • #8
                Originally posted by cwhenning
                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.

                Comment


                • #9
                  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?
                  http://youtu.be/YtpkrIS4Sig?hd=1

                  Comment


                  • #10
                    Originally posted by usualsuspects
                    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.
                    "Probably the worst thing that ever happened to malaria in poor nations was its eradication in rich ones." -- Tina Rosenberg

                    Comment


                    • #11
                      Originally posted by 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...
                      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!

                      Comment


                      • #12
                        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.

                        Comment

                        Working...
                        X