Welcome Guest
Log In | Register )
You last visited January 24, 2017, 6:04 am
All times shown are
Eastern Time (GMT-5:00)

Excel Help Needed

Topic closed. 7 replies. Last post 6 years ago by Rakster.

Page 1 of 1
PrintE-mailLink
Rakster's avatar - praying hands.jpg
Saskatchewan
Canada
Member #19992
August 9, 2005
2867 Posts
Offline
Posted: July 26, 2010, 10:48 am - IP Logged

Is there a way to get a column to organize a set of numbers from highest ranking to lowest by using data from an existing column?

For example...

 

1 (1 is in column A9) has 4 (4 is in column E9)hits

2 (2 is in A10) has 1 (1 is in E10) hit

3 has 6 hits

4 has 8 hits etc.

I want a new column to organize the 1,2,3,4 etc in an order according to the number of hits they had (8,6,4,2 etc)

 

Is there a formula to do this?

 

Thanks

We are all Lucky... just some of us don't realize it!


    United States
    Member #93947
    July 10, 2010
    2180 Posts
    Offline
    Posted: July 26, 2010, 1:03 pm - IP Logged

    Is there a way to get a column to organize a set of numbers from highest ranking to lowest by using data from an existing column?

    For example...

     

    1 (1 is in column A9) has 4 (4 is in column E9)hits

    2 (2 is in A10) has 1 (1 is in E10) hit

    3 has 6 hits

    4 has 8 hits etc.

    I want a new column to organize the 1,2,3,4 etc in an order according to the number of hits they had (8,6,4,2 etc)

     

    Is there a formula to do this?

     

    Thanks

    In my experience, what you are trying to do here is much more easily accomplished with a short, throw-away program in a language like GWBASIC.  This is an old, legacy piece of software, but it allows you to write "quick and dirty" programs to scan "Flat Files" like the one you're dealing with easily.  GWBASIC is available from numerous sites for free.  Be sure you download it from a TRUSTED site!  It consists of one executable image, requiring no installation, per se, and you can run it from the Command Prompt.  Programmer's Guides are also out there.


      United States
      Member #79057
      August 26, 2009
      70 Posts
      Offline
      Posted: July 26, 2010, 1:08 pm - IP Logged

      Don't know if I understand you correctly, but if you've got:

       

      columns             A            B            C           D

      Row #1             4            3            8            6

       

      you can put this in order by typing:

       

      =large(a1:d1,1)

      tells the cell to look for the largest number in the array a1:d1

      =large(a1:d1,2)

      tells the cell to look for second largest number in same array

      =large(a1:d1,3) - third largest and so on.

      You can also do Small in the same format.

       

      Hope that helps.

        MzDuffleBaglady's avatar - Lottery-018.jpg

        United States
        Member #81314
        October 16, 2009
        19464 Posts
        Offline
        Posted: July 26, 2010, 1:12 pm - IP Logged

        Click on ascending order, or descending order, it can be found in the upper right hand corner of the ribbon, "Sort and Filter button".

        Click inside of the cell you want to sort, or the column.

         

        Good Luck.

         

        P.S. also try, "Conditonal Formating",

        Yes, conditional formating.

        You will have to set up the formula, for what you want,

        < 8

        > 6

        etc.

        and it will calculate it for you, but, you have to use, "Conditional Formating to get the answer".

         

            123     789
        345704
        452678
        455678
        468506
        506468
        678455
        678452
        704345
        789123
        ascendingdescend
        order
        L to HH to L

        The Struggle is real!

          Rakster's avatar - praying hands.jpg
          Saskatchewan
          Canada
          Member #19992
          August 9, 2005
          2867 Posts
          Offline
          Posted: July 26, 2010, 1:28 pm - IP Logged

          Thank you all for your help. Its greatly appreciated!

          We are all Lucky... just some of us don't realize it!

            Rakster's avatar - praying hands.jpg
            Saskatchewan
            Canada
            Member #19992
            August 9, 2005
            2867 Posts
            Offline
            Posted: July 26, 2010, 1:47 pm - IP Logged

            Don't know if I understand you correctly, but if you've got:

             

            columns             A            B            C           D

            Row #1             4            3            8            6

             

            you can put this in order by typing:

             

            =large(a1:d1,1)

            tells the cell to look for the largest number in the array a1:d1

            =large(a1:d1,2)

            tells the cell to look for second largest number in same array

            =large(a1:d1,3) - third largest and so on.

            You can also do Small in the same format.

             

            Hope that helps.

                      D1    D2    D3    total

            1         0     1     2          3 

            2         0     1     1          2

            3         1     1     2          4

            4         1     0     2          3

            5         0     2     0          2

            6         0     0     0          0

            7         1     1     0          2

            8         2     0     0          2

                     2     1     0          3

            0         0     0     0          0

             

            Above is a list of digits (1-0) top down in bold.

            The 1 was drawn a total of 3 times . Once in D2 (digit 2 position) and twice in D3 position for a total of 3 times.

            The 2 was drawn a total of 2 times etc.

            I want the DIGITS (1,2,3,4,5,6,7,8,9,0) to correspond to the number of times they were drawn... so the most to the least...

            Like the 3 was the most hit with a total of 4 times, then the 1,4,9 all hit 3 times so I want them to follow in order from most to least... so 3, 1,4,9,2,5,7,8,6,0 in that sort of order.... those that are the same total like 1,4,9 with 3 hits would order first above those that hit with 2 hits like 2,5,7,8

            Is there a formula I can impliment to get this done?

            Thanks for your help.

            We are all Lucky... just some of us don't realize it!

              Raven62's avatar - binary
              New Jersey
              United States
              Member #17843
              June 28, 2005
              51255 Posts
              Offline
              Posted: July 26, 2010, 3:24 pm - IP Logged

              Is there a way to get a column to organize a set of numbers from highest ranking to lowest by using data from an existing column?

              For example...

               

              1 (1 is in column A9) has 4 (4 is in column E9)hits

              2 (2 is in A10) has 1 (1 is in E10) hit

              3 has 6 hits

              4 has 8 hits etc.

              I want a new column to organize the 1,2,3,4 etc in an order according to the number of hits they had (8,6,4,2 etc)

               

              Is there a formula to do this?

               

              Thanks

              Cell A11 =RANK($A9,$A$9:$E$9,1) copy/paste into Cell B11 thru E11

              Cell A12 =RANK($A9,$A$9:$E$9,1)

              Cell B12 =RANK($B9,$A$9:$E$9,1)+COUNTIF($A$11:A$11,RANK($B$9,$A$9:$E$9,1) copy/paste into Cell C12 thru E12

              A mind once stretched by a new idea never returns to its original dimensions!

                Rakster's avatar - praying hands.jpg
                Saskatchewan
                Canada
                Member #19992
                August 9, 2005
                2867 Posts
                Offline
                Posted: July 26, 2010, 10:44 pm - IP Logged

                Cell A11 =RANK($A9,$A$9:$E$9,1) copy/paste into Cell B11 thru E11

                Cell A12 =RANK($A9,$A$9:$E$9,1)

                Cell B12 =RANK($B9,$A$9:$E$9,1)+COUNTIF($A$11:A$11,RANK($B$9,$A$9:$E$9,1) copy/paste into Cell C12 thru E12

                Thanks Raven!

                We are all Lucky... just some of us don't realize it!