Results 1 to 5 of 5

Thread: Unique Ranking
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2008
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Unique Ranking

    I'm trying to Rank a list and than re-rank the list while excluding certain (or by Criteria) items

    Vendor Co Cost Fee Rank
    Vertox 500 4
    BV 1520 3
    A&S 3057 2
    ISA 5000 1
    GCC 250 5
    GSI 147 6
    Excluded Vendors
    ISA
    A&S
    Vendor Co Cost Fee Rank
    BV 1520 1
    Vertox 500 2
    GCC 250 3
    GSI 147 4

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Posts
    768
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique Ranking

    assuming vendor is in A1.

    Code:
    =RANK(B2,$B$2:$B$7)

  3. #3
    Board Regular
    Join Date
    Jul 2008
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique Ranking

    Quote Originally Posted by jamtay317 View Post
    assuming vendor is in A1.

    Code:
    =RANK(B2,$B$2:$B$7)

    Thanks, but looking for alittle more. I need to know how to rank by excluding whatever vendors are listed under the excluded vendors.

    So if BV was also listed under excluded vendors it would be ommited from the final ranking list
    . and the revised list would look like (below)
    Vendor Co Cost Fee Rank
    Vertox 500 1
    GCC 250 2
    GSI 147 3

  4. #4
    Board Regular
    Join Date
    Mar 2013
    Posts
    768
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique Ranking

    maybe something like this assuming your exceptions are in e3:e4

    =SUMPRODUCT(--(ISNA(MATCH($A$3:$A$8,$E$3:$E$4,0))),--(B3<$B$3:$B$8))+1

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Unique Ranking

    Vendor Co Cost Fee Rank Vendor Co Cost Fee Rank
    Vertox 500 4 Vertox 500 2
    BV 1520 3 BV 1520 1
    A&S 3057 2 GCC 250 3
    ISA 5000 1 GSI 147 4
    GCC 250 5
    GSI 147 6
    Excluded Vendors
    ISA
    A&S

    F2, control+shift+enter, not just enter, and copy down:
    Code:
    =IFERROR(INDEX($A$2:$A$7,SMALL(IF(1-ISNUMBER(MATCH($A$2:$A$7,$A$10:$A$11,0)),
      ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(F$2:F2))),"")
    
    G2, just enter and copy down:
    Code:
    =IF($F2="","",VLOOKUP($F2,$A$2:$B$7,2,0))
    
    H2, just enter and copy down:
    Code:
    =RANK($G2,$G$2:$G$5)+COUNTIF($G$2:G2,G2)-1
    
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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