Page 2 of 2 FirstFirst 1 2
Results 11 to 13 of 13

Thread: How to show a list of top 10 pairing names from far left column and top row

  1. #11
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013


    Register for a FREE account, and/
    or Log in to avoid these ads!

    I tested the formulas you provided by changing the % values on the sample data and there appears to be a flaw in Rank 7 & 8 as it duplicates the results. I'm not sure where the error lies.

    Please see attached sample data.
    Attached Files Attached Files

  2. #12
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    882
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Chi View Post
    there appears to be a flaw in Rank 7 & 8 as it duplicates the results.
    You should've taken into account all possible situations and write the expected results?
    Look at my idea with the helper columns (of course you can hide this helper columns).

    The array formula in the 'O' column is the following
    Code:
    =IFERROR(INDEX($B$1:$F$1;1;SMALL(IF(INDEX($B$2:$F$6;MATCH(M2;$A$2:$A$6;0);)=L2;COLUMN($A$1:$E$1));1));"")
    The formula in 'P' column is the next
    Code:
    =IF(IF(AND(M2=M1;O1=O2);COUNTIF($P$1:P1;">0")+1;0)=0;1;P1+1)
    The array formula in the 'O' column is the following
    Code:
    =IFERROR(INDEX($B$1:$F$1;1;SMALL(IF(INDEX($B$2:$F$6;MATCH(M2;$A$2:$A$6;0);)=L2;COLUMN($A$1:$E$1));P2));"")
    There is probably a simpler solution without extra columns but so much from me.
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #13
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    Thank you. This did the trick

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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