none
Creating a table to rank & formulate a series of numbers RRS feed

  • Question

  • I'm trying to create a table which will give the result as shown above.

    Basically there are upto 3 or 4 separate selections of 4 numbers each. First selected is worth 4 points. 2nd is worth 3 points & so on.

    What i'd like to achieve is a list of the selected numbers ranked according to their value based on the total "points". As above "7" is the highest ranked number with 10 "points".

    I'm sure its quite easy & think i've done something similar in the past, but am drawing a real blank at the moment.

    Any help greatly appreciated

    Thanks in advance


    It is better to temporarily fail with a plan that will ultimately succeed than to temporarily succeed with a plan that must ultimately fail

    Sunday, October 20, 2019 1:19 AM

Answers

All replies

  • Excel 2010 to 2019 Power Query
    No formulas, no VBA macro.
    http://www.mediafire.com/file/66k53yhj5bp2ms2/10_20_19.xlsx/file

    • Marked as answer by Smart seal Monday, October 21, 2019 9:14 AM
    Sunday, October 20, 2019 1:54 PM
  • Hi,

    Please refer to Herbert’s reply to have a check. 

    In addition, you could also try to use formulas and Sort & Filter to get the list if needed. 

    Step 1: Type the formula in E2: =LARGE($B$2:$D$5,SUM(COUNTIF($B$2:$D$5,$E$1:E1))+1), then press Ctrl, Shift and Enter at the same time to finishing typing. Then you could pull down the cell until “#NUM!” appers. It could get the non-repeating selections.

    Step 2: Type the formula in F2, =SUMPRODUCT(($A$2:$A$5)*($E2=$B$2:$D$5)). It could get the total points of the Selections.

    Step 3: Copy the range E2 to F7, past to other cells and select Values only.

    Step 4: Select the list includes the titles > Click Sort & Filter > Custom Sort the > Sort by “Tot Val” to rank the list.

    If you have any updates, please feel free to let me know.

    Best Regards,

    Emily 


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Monday, October 21, 2019 6:28 AM
  • Thanks Herbert it is exactly what i was after. I just have to try to work out the processes behind it so i can learn to problem solve some of these myself.

    I also want to apply it to a bigger scale so if i get stuck may seek some further guidance.

    Thanks again, your knowledge & help is greatly appreciated. 

    Have a good day/week

    Regards

    Andrew


    It is better to temporarily fail with a plan that will ultimately succeed than to temporarily succeed with a plan that must ultimately fail

    Monday, October 21, 2019 9:17 AM
  • Hi Emily

    Thanks for the response & step by step guide, I will try your suggestion over the coming days & see if i can adapt it to my end goal.

    Thanks again, your help is appreciated

    Have a good day

    Andrew


    It is better to temporarily fail with a plan that will ultimately succeed than to temporarily succeed with a plan that must ultimately fail

    Monday, October 21, 2019 9:19 AM
  • Hi,

    Thanks for your response. And I am glad that your problem has been resolved.

    If you have any questions about Excel, please feel free to ask questions in this forum to discuss with us.

    Best Regards,

    Emily 


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Tuesday, October 29, 2019 12:24 PM