+ Reply to Thread
Results 1 to 10 of 10

Getting rid of duplicated rank?

  1. #1
    Registered User
    Join Date
    07-06-2021
    Location
    Riga, Latvia
    MS-Off Ver
    2019
    Posts
    5

    Question Getting rid of duplicated rank?

    Hello all,
    Newbie here.

    Yearly I organize a card game event in my hometown in Latvia, where I gather around 20 players. We call it "Zole".
    It's interesting card game, I guess, only played in Latvia, so we have even tournaments for it there.

    This card game is usually played by 3 or 4 players, so we need clear rank to know which table they need to take - 1st table if 3rd place, 2nd table if 6th place and so on.

    In attached file you will find 2 tables.
    Top table is most important, with round points - based on them, you get rank. There are 8 rounds by 30 mins.
    Problem is that sometimes rank duplicates, so I need to manually check bottom table with secondary points (bigger the points, higher the rank)(for example, as we got five 7th places, we clarify they rank by checking their secondary points).

    Is there any option without getting in deep programming to get clarified rank (column M) right away?
    Meaning - If there is duplicate in column L, then check secondary table (column K K20:K35).
    Attached Files Attached Files
    Last edited by linrdsan; 07-06-2021 at 05:12 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Getting rid of duplicated rank?

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-06-2021
    Location
    Riga, Latvia
    MS-Off Ver
    2019
    Posts
    5

    Re: Getting rid of duplicated rank?

    Hello, just did it.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Getting rid of duplicated rank?

    Maybe change your total points calculation to this:

    =SUM(C2:J2)+(RANK(INDEX($K$20:$K$35,MATCH(A2,$A$20:$A$35,0)),$K$20:$K$35,0)/100)

    For your locale, you may need to change commas to semi-colons.

  5. #5
    Registered User
    Join Date
    07-06-2021
    Location
    Riga, Latvia
    MS-Off Ver
    2019
    Posts
    5

    Re: Getting rid of duplicated rank?

    Hey, thanks for quick reply, but seems it won't fit.

    Attachment 739153

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Getting rid of duplicated rank?

    Attach the workbook, NOT a picture of it!

    Try this instead:

    =SUM(C2:J2)+(RANK(INDEX($K$20:$K$35,MATCH(A2,$A$20:$A$35,0)),$K$20:$K$35,1)/100)
    Last edited by AliGW; 07-06-2021 at 04:59 AM.

  7. #7
    Registered User
    Join Date
    07-06-2021
    Location
    Riga, Latvia
    MS-Off Ver
    2019
    Posts
    5

    Re: Getting rid of duplicated rank?

    Holy smokes, it works correctly. Did not expect to get help so soon, thanks!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Getting rid of duplicated rank?

    Modified Ali's formula:

    Put in "helper" column

    =L2+(RANK(INDEX($K$20:$K$35,MATCH(A2,$A$20:$A$35,0)),$K$20:$K$35,0)/100)

    then RANK on this column
    Last edited by JohnTopley; 07-06-2021 at 05:04 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Getting rid of duplicated rank?

    Quote Originally Posted by linrdsan View Post
    Holy smokes, it works correctly. Did not expect to get help so soon, thanks!
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  10. #10
    Registered User
    Join Date
    07-06-2021
    Location
    Riga, Latvia
    MS-Off Ver
    2019
    Posts
    5

    Re: Getting rid of duplicated rank?

    Yes, I did so. Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] delete rows contain duplicated data or non duplicated by inputbox
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-25-2020, 04:01 PM
  2. Macro shows duplicated entries but sometimes they are not duplicated due to data.
    By tweacle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2020, 04:47 PM
  3. Rank range but use only one instance of duplicated ranking
    By nigelog in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-05-2016, 01:02 PM
  4. How to Rank duplicated categories in a list
    By adambriggs in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2015, 02:31 PM
  5. Replies: 6
    Last Post: 03-26-2012, 05:12 PM
  6. Rank amounts and duplicated in descending order
    By jimstrongy in forum Excel General
    Replies: 3
    Last Post: 01-19-2012, 03:29 PM
  7. getting duplicated values depending on rank order
    By zbor in forum Excel General
    Replies: 2
    Last Post: 05-28-2009, 03:28 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1