Results 1 to 10 of 13

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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

    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,235
    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,235
    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!

Posting Permissions

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