Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts

    Listing items in order of preference

    I am trying to do something in Excel but don't know what the function is called, so I can't look up directions on how to do it.

    In the example I attached, I am listing topics for discussion in column A. Someone will rate them in column C, from 1 to 5 in order of preference. Elsewhere on the sheet I want the topics to be shown in order, based on the number put in column C.

    Thanks
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Vincenzo,

    You can do it with a Index/Match combination:

    =INDEX(Topics,MATCH(1,Rankings,0),1)

    Note: You need to change MATCH(1,Rankings... to MATCH(2,Rankings...) etc.

    Vincenzo.PNG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Vincenzo (2016-02-02)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi RG

    ..but what happens if two (or more) topics are, say, given the same ranking????
    (I know the poster didn't ask for this)

    zeddy

  5. The Following User Says Thank You to zeddy For This Useful Post:

    Vincenzo (2016-02-02)

  6. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Chinese Proverb: "Don't cross the bridge until you come to it."

    Microsoft Quote: "It's not a bug, it's a FEATURE!"

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #5
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    RG,
    Thanks for the suggestion. I've been reading about the Index/Match combination, my brain is having trouble getting wrapped around it, I'm getting closer tho.

    But it is possible that multiple topics can have the same ranking.

    Is there a workaround for that?

    Thanks

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Vincenzo,

    Ok you made me do it! I had to resort to VBA.

    Code:
    Option Explicit
    
    Sub OrderAndCopy()
    
        Dim lFirstTopicRow As Long
        Dim lLastTopicRow  As Long
        
        Application.ScreenUpdating = False
        
        Range("TopicRanks").Select
        With Selection
            lFirstTopicRow = ActiveCell.Row
            lLastTopicRow = .Rows.Count + lFirstTopicRow - 1
        End With
        
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("C" & Format(lFirstTopicRow) & _
                                      ":C" & Format(lLastTopicRow)), _
                         SortOn:=xlSortOnValues, _
                          Order:=xlAscending, _
                     DataOption:=xlSortNormal
            .SetRange Range("TopicRanks")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
            
        End With   'ActiveWorkbook.Worksheets("Sheet1").Sort
        
        Range("A" & Format(lFirstTopicRow) & _
              ":A" & Format(lLastTopicRow)).Copy Destination:=Range("OrderedTopics")
        Application.CutCopyMode = False
        
        [A1].Select
        
        Application.ScreenUpdating = True
        
    End Sub   'OrderAndCopy
    In the attached sample file the macro is attached to the blue button. Just change the numbers in Column C and click the button. BTW: I don't know why you are leaving blank columns like "Column B" but personally I would recommend against this practice as it can lead to problems IMHO. Like me putting the order numbers in column "B" and writing the code on that basis and then realizing that I should have been using column "C"!

    Also please note the use of the RangeNames TopicRanks and OrderedTopics.

    Test File: Vincenzo Sample.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Vincenzo (2016-02-02)

  10. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Vincenzo

    Attached is a small workbook that does what you want without any macros.

    RG: another saying (probably not Chinese nor Microsoft) - "to macro, or not to macro - that is the question."

    A few words of explanation:
    - col C is present only to show you how the RANK function works "normally;" it can be omitted. Because of the possibility of tie-breakers, you want to use col D for ranking to create unique ranks. It uses the COUNTIF function added to RANK for this purpose. (I have to admit I got this from somewhere else.)

    - You never stated whether 5 was most or least interest. I assumed that the higher rating meant more interest (5 is the most interest); if it's the reverse, then you'd need to put in a third argument (any non-zero #) before the close parens of the RANK function in col D; eg, D2 would become =RANK(B2,$B$2:$B$10,1)+COUNTIF(...) (Also do this in col C if you just want to see how RANK works by itself.)

    - This also uses the index-match combination that RG used. I use ROW()-1 in the way that RG uses the numbers 1,2,3, etc.
    --- I'm storing the sorted list of topics beginning at Row 2 so you can see how the sorted list lines up with the original list. ROW() gives you the current row #, so this is returning the numbers 2,3,4,... By subtracting 1, you get 1,2,3... as RG has. This is ONLY bcs I chose to store the topics starting in row 2. If you want to store the topics starting in row 13, as in your original spreadsheet, you'd do ROW()-12 starting in your location of F13. This also gives you the numbers 1,2,3, etc. Regardless of where you store the topics or using RG's "hard-coded" 1,2,3, you need that 1,2,3 sequence. Basically, these 1,2,3 #s correspond to the topic with the most interest, the topic with the 2nd-most interest, etc.

    --- the MATCH function looks for the # per above in col D (the ranks with tie breaker). It returns the row within col D that has the value of ROW()-1

    --- the INDEX function takes that row # from the MATCH function and finds the topic in that same row in Col A.


    Hope this helps.

    Fred
    Attached Files Attached Files

  11. The Following User Says Thank You to fburg For This Useful Post:

    Vincenzo (2016-02-02)

  12. #8
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    Quote Originally Posted by RetiredGeek View Post
    Chinese Proverb: "Don't cross the bridge until you come to it."
    I've always wondered where that saying came from!

    Quote Originally Posted by RetiredGeek View Post
    Microsoft Quote: "It's not a bug, it's a FEATURE!"
    I knew about that one!

    Jim

  13. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi RG

    ..but what happens if two (or more) topics are, say, given the same ranking????
    (I know the poster didn't ask for this)
    I like your quotes. My favourite is from Paul Getty Junior (I think) who said the three secrets to a successful life are
    1. Rise early
    2. Work hard
    3. Strike oil

    ..now, it seems that I anticipated the posters subsequent point!
    So I'll now raise the issue of:
    ..should subjects of equal ranking be listed in subject alphabetic order, or by first-come-first-served order (i.e. as per original list order)

    I put it to you that this might need a radio-button to select the appropriate choice.
    I rest my case.

    zeddy

  14. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    REST ... Me thinks the ball is squarely in your court to solve this one!

    "You can do it!...You have the Technology!"

    ROTFLOL.gif
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2016-02-01)

  16. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi RG

    ..I accept the challenge, and will assemble my team tomorrow to start this project.
    I will apply for a grant from USAID, UNDP or the European Commission and expect to submit a full proposal for the solution shortly.

    (..maybe I'll just post the workbook tomorrow)

    zeddy

  17. #12
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    Thanks, RG and fburg. I'm going through these three ways of doing it, trying to see which one I can get a handle on. Everything I know about VBA will fit in the period at the end of this sentence, so I am trying to avoid that option.

    As far as ties in ranking go, I am not picky about that, any way of breaking the tie will work.

    RG,Will your first, non-VBA, suggestion work given that I don't care how ranking ties are resolved, or will it have a problem with ties in general? I am still trying to figure out how to apply it. Did you intend for me to replace the "1" and the "2" in your example with the ranking numbers from 1 to 5? If that is the case, then there may be a problem. I simplified what we are trying to do in my first post, thinking I could adjust the solution later to my needs. I see now that was a mistake, I did not realize that this was a complex task.

    In reality the rankings column can actually include any number from 1 to 100, and not all those numbers will show up. There will only be about 10 topics, which would then put 10 numbers in the rankings column, and they can be anything from 1 to 100, with duplication of numbers possible.

    Thanks
    Last edited by Vincenzo; 2016-02-01 at 22:23.

  18. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Vincenzo View Post
    I am still trying to figure out how to apply it. Did you intend for me to replace the "1" and the "2" in your example with the ranking numbers from 1 to 5?

    The formula shown would go into the selected cell as shown in the graphic. You would then copy the formula down. After that is accomplished you would go to the next row down and change the 1 to a 2 then to the next row down and change the 1 to a 3, etc.

    In reality the rankings column can actually include any number from 1 to 100, and not all those numbers will show up. There will only be about 10 topics, which would then put 10 numbers in the rankings column, and they can be anything from 1 to 100, with duplication of numbers possible.

    This is information which should have been provided initially as it drastically changes how the sheet should be setup either with formulas or VBA. With that many items VBA may be your best bet as it can be made to adjust to differing sizes much better that formulas IMHO. Also if you have that many items it may be best to display the results on a separate sheet.

    So how about a full disclosure on the parameters of your requirement.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  19. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Vincenzo,

    Here is a variation of RG's Index/Match formula using a helper column that ranks. The Ranks (column A will handle duplicates by assigning a higher rank to the first instance of the duplicate, next rank to the second instance, and so on.

    Using your setup in the sample. ADD a new column A. In A3, add the formula

    Code:
    =RANK(D3,$D$3:$D$7)+COUNTIF(D$3:D3,D3)-1
    and copy down. This will give the ranking while handling the duplicates

    Vincenzo1.png

    In G3, make a column of ordered numbers (1-5 in this example) while in H3, enter this formula and copy down:

    Code:
    =INDEX($A$3:$B$7,MATCH(G3,$A$3:$A$7,0),2)
    vincenzo2.png

    Your topics will now be in order according to ranking with ties in the order of their instance (See above image)

    HTH,
    Maud
    Attached Files Attached Files

  20. The Following User Says Thank You to Maudibe For This Useful Post:

    Vincenzo (2016-02-02)

  21. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hey Maud - I think that's what I did. I knew I saw this somewhere!

    As far as having ranking values from 1 to 100, that won't matter to the solution I posted.

    As far as having a variable number of topics, I did not take that into account but it would not be hard to do. I'll leave that for another time.

    However, I would question having rankings of importance from 1-100, especially if there are only 10 topics. I'm not suggesting that you need to limit the "importance values" to 10 numbers just bcs you have 10 topics. But my experience has shown that people really can't make much of a distinction between something that has an importance of, say, 87 vs 88. A small set of numbers should suffice.

    Fred

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •