Listing items in order of preference

Topic

New Reply

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

Reply | Quote

Replies

Vincenzo,

You can do it with a Index/Match combination:

[noparse]=INDEX(Topics,MATCH(1,Rankings,0),1)[/noparse]

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

43460-Vincenzo

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Zeddy,

Chinese Proverb: “Don’t cross the bridge until you come to it.”

Microsoft Quote: “It’s not a bug, it’s a FEATURE!”

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Chinese Proverb: “Don’t cross the bridge until you come to it.”

I’ve always wondered where that saying came from!

Microsoft Quote: “It’s not a bug, it’s a FEATURE!”

I knew about that one!

Jim

Group "L" (Linux Mint)
with Windows 10 running in a remote session on my file server

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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: 43463-Vincenzo-Sample

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Zeddy,

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

“You can do it!…You have the Technology!” Paraphrased from the Six Million Dollar Man
35623-ROTFLOL
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

..so in addition to the other methods given, my attached version uses VBA advanced filter to extract the data to a specified location, and then apply a sort.
If Topics have the same Rating value (1 to 100), then, by default, the order will be as in the original list.
If a checkbox is ticked, then Topics with the same Rating will be in alphabetic order.
Any Topics that are unrated will appear at the bottom of the list.

zeddy
PS: It’s like groundhog day here

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

43474-Vincenzo1

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)

43475-vincenzo2

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

HTH,
Maud

Reply | Quote

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

Reply | Quote

Fred,

I didn’t open your file but got lost in the wording of your post. Yes, looks like I duplicated your solution. Nicely done!

Maud

Reply | Quote

Thanks to all of you guys for the help. A lot of this goes way above my level of understanding, but think I have the info I need to get my sheet working now. I ruled out the VBA suggestions because that is way above me, and out of the others, I found that I could get my head around Maudibe’s technique the easiest, mainly I think because it did not use the Row references.

RG, you asked me for more info on this – My wife is a psychology teacher and this spreadsheet is something she is going to use in a talk she is doing. When I originally posted, we were not sure how the rating column would be determined, so I just took a guess and posted that the ratings would be 1 to 5, thinking (incorrectly) that it would not make much difference in the solution, and I could just change it later from 1-5 to whatever number it ends up being, once I was further along in developing the sheet. I apologize that this misled you down the wrong path. The rating became the seemingly odd 1 – 100 because she is using multiple criteria and combining them to get the final rating.

fburg, thanks for the explanation of MATCH and INDEX, that puts me further along in understanding these functions that are all new to me.

I do need one more thing, if I could. I’d like to have a column next to the ranked topics that would show the rating values of each topic (essentially the numbers from column D). I intend to hide col G, so this would then be next to the ranked topics. I’m not sure how to integrate this into what has already been done here. If someone could help me here, I would much appreciate it.

I’ve attached a modified version of Maudibe’s sheet that shows this.

Thanks

Reply | Quote

Vincenzo,

Fred actually beat me to your solution. I wasn’t aware we had had the same idea in mind.

I have adjusted the formulas in Column A to reference values from Column F instead of D. You can hide column G now if you like

Maud

Reply | Quote

Maudibe,

I need for the column D ratings to stay where they are, next to the topics, so the ratings can be filled in there and changed as needed. Then next to the ranked list on the right, I need to see the ratings listed there. It will be the same numbers as from column d, but rearranged in the new (ranked) order, corresponding to the topics next to them.

Thanks

Reply | Quote

Hi Maud,

The explanation was for Vincenzo’s benefit to explain INDEX/MATCH. Anytime I use this combination, I have to think at least twice. It doesn’t fall trippingly off the tongue for me.

No problem – as I said, I had this in my bag of tricks from somewhere else thinking it would come in handy some time.

Vincenzo: attached is a revised spreadsheet with what you asked for. I chose to use the VLOOKUP function. It also could have been done with INDEX/MATCH. I decided to use VLOOKUP bcs I think it’s a little more straightforward than INDEX/MATCH; also your data layout was ok to use this. Had the original Ratings been to the left of the topics, VLOOKUP would not have worked.

Note that you have to be careful with any approach when extending this to the full solution.

Fred

Reply | Quote

Thanks, Fred, that is just what I needed. I’m creating my final version now.

And thanks again to everybody who helped here. My wife has a tendency to commit to creating something, thinking that I can always figure out the details. Thanks for bailing me out!

Reply | Quote

Vincenzo – you can play this 2 ways.

If you want to look like the hero all the time, don’t tell her about the Lounge and you’re set for life.

If you don’t want to bother with us (old) foggies, tell her to join the Lounge.

Fred

Reply | Quote

Fred

..are you mad???? His wife is a psychology teacher. We could get locked up.

Vincenzo – please don’t tell your wife about us

Reply | Quote

Fred,

I’m already her hero, even though she knows I tapped you guys’ brains.

zeddy, she won’t get you locked up, but she might try to analyze you.

Reply | Quote