Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

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

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

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



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

    Hi

    I was trying to figure out a formula that can be used to show a list of the top 10 pairing names, along with their % value, from the far left column and top row based on the % data in the grid like in this example below:

    Name Adam S Jane L Lee M Tim E Ann D

    Sarah B 10% 47% 11% 85% 61%
    Tom D 2% 19% 0% 20% 15%
    Adrian T 27% 71% 43% 57% 8%
    Peter M 0% 99% 6% 13% 33%
    Amy O 95% 18% 55% 30% 2%

    I know Hlookups and Vlookups would not suffice as they would return the value at interception rather than the reverse I'm looking for.

    I hope someone can help me.

    Kind regards
    Chi

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013

    Return Row header and Column header if match criteria

    Quote Originally Posted by Chi View Post
    I hope someone can help me.
    This is not your first thread.
    You should set an example of your workbook and the expected results. It would be clearer.
    Does anyone else have to enter data for you to help you?

    For the names from the rows, set the ARRAY formula (copy down)
    Code:
    =IF(ROWS(A$1:A1)>COUNTIF($B$2:$F$6;$I$1);"";INDEX($A$2:$A$6;SMALL(IF($B$2:$F$6=$I$1;ROW($B$2:$F$6)-ROW($B$2)+1);ROWS(A$1:A1))))
    For the names from the columns, set ARRAY the formula (copy down)
    Code:
    =IF(I2="";"";INDEX($B$1:$F$1;MATCH($I$1;INDEX($B$2:$F$6;MATCH(I2;$A$2:$A$6;0);0);0)))
    Is this a solution for you?
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	chi-navic9784.png 
Views:	4 
Size:	8.4 KB 
ID:	8864  
    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. #3
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    The formula provided assumes the pairing names when you enter a % number which is not what I was asking. I've attached a sample of what I'm hoping to achieve.

    Thanks
    Chi
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Pairings.JPG 
Views:	2 
Size:	44.8 KB 
ID:	8866  

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,237
    Articles
    0
    Excel Version
    Office 365 Subscription
    Attach the workbook, not a picture of it.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    Workbook attached.

    Hopefully, I'll have a response from someone who can help me.
    Attached Files Attached Files

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,237
    Articles
    0
    Excel Version
    Office 365 Subscription
    I hope you aren't suggesting that I can't help or was being unhelpful.

    Excel 2016 (Windows) 32 bit
    L
    M
    N
    2
    Peter M
    Jane L
    99%
    3
    Amy O
    Adam S
    95%
    4
    Sarah B
    Tim E
    85%
    5
    Adrian T
    Jane L
    71%
    6
    Sarah B
    Ann D
    61%
    7
    Adrian T
    Tim E
    57%
    8
    Amy O
    Lee M
    55%
    9
    Sarah B
    Jane L
    47%
    10
    Adrian T
    Lee M
    43%
    11
    Peter M
    Ann D
    33%
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    L
    M
    N
    2
    =INDEX($A$2:$A$6,SUMPRODUCT(($B$2:$F$6=N2)*ROW($A$2:$A$6))-COLUMN($A$2))
    =INDEX($B$1:$F$1,SUMPRODUCT(($B$2:$F$6=N2)*COLUMN($B$1:$F$1))-COLUMN($B$2)+1)
    =LARGE($B$2:$F$6,ROW(1:1))
    Sheet: Sheet1

    This will only work properly if there are no duplicate percentages, as per the sample data provided.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    This is great!

    It works a dream but I would need the formula(s) to accommodate % values that appear more than once in the data. I know the sample data I had attached did not show this but this would be very important to appear in the top 10 pairing names.

    Is this possible to do?

  8. #8
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,237
    Articles
    0
    Excel Version
    Office 365 Subscription
    I thought this might be the case. Please provide sample data that properly reflects the real data. If something is 'very important' and you don't include it, then we cannot provide a solution that takes it into account. You need to tell us everything that is important. Try again with the sample, please, showing how you want to deal with matching values, as it is an entirely different problem to solve.
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #9
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    I've attached a sample data as requested which shows matching % values.
    Attached Files Attached Files

  10. #10
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013

    Return Row and Column headers if match duplicate values in range

    Quote Originally Posted by Chi View Post
    shows matching % values.
    There is an error in your expected results, so there is one missing value.
    Try this formulas below.

    For the Rank
    Code:
    =IFERROR(LARGE($B$2:$F$6;ROW(A1));"")
    For the Row headers (CSE formula)
    Code:
    =IFERROR(INDEX($A$2:$A$6;SMALL(IF($B$2:$F$6=L2;ROW($1:$5));COUNTIF(L$2:L2;L2)));"")
    For the Column headers (CSE formula)
    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));"")
    Did this help?
    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

Page 1 of 2 1 2 LastLast

Posting Permissions

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