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

Chi

New member
Joined
Jun 23, 2015
Messages
29
Reaction score
0
Points
3
Excel Version(s)
excel 2013
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
 
Return Row header and Column header if match criteria

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?
 

Attachments

  • chi-navic9784.png
    chi-navic9784.png
    8.4 KB · Views: 10
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
 

Attachments

  • Pairings.JPG
    Pairings.JPG
    44.8 KB · Views: 6
Workbook attached.

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

Attachments

  • Pairings.xlsx
    8.7 KB · Views: 6
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.
 
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?
 
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.
 
I've attached a sample data as requested which shows matching % values.
 

Attachments

  • Pairings.xlsx
    9.7 KB · Views: 6
Return Row and Column headers if match duplicate values in range

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?
 

Attachments

  • chi-navic9784-2.xlsx
    13.1 KB · Views: 10
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.
 

Attachments

  • chi-navic9784 2.xlsx
    13.1 KB · Views: 4
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.
 

Attachments

  • chi-navic9784-3.xlsx
    15.4 KB · Views: 6
Thank you. This did the trick :smile:
 
Back
Top