Rank in order with duplicates

L

Legacy 461286

Guest
I have a table containing 5 rows the headers for each are in column A. Each row contains values that are summed up and this is displayed in the total column (Column H).

I want to show in a separate table below the order/rank of these from 1 to 5. I have managed to do this so far by

=INDEX($A$2:$A$6,MATCH(ROWS($H$2:H2),$I$2:$I$6,0))

This works fine if the totals for each row are different. If I have any duplicates i then encounter an issue as one of the row names is not displayed.

Ideally I would be able to show any duplicates in the same cell e.g.
1st - Row1
2nd - Row2
3rd - Row3, Row4
4th - Row5
5th -

can anyone help me out?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
To get the duplicates in a single cell would need the TEXTJOIN function, which, unfortunately, is not available in excel 2016.

You would need to use a multiple column approach for the results as shown in the simple example below,
Book1
ABCDEFGHI
2aa 1
3bbc2
4cd 2
5de 3
6e4
Sheet10
Cell Formulas
RangeFormula
C2:D5C2=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($I$2:$I$6)/(ROWS(C$2:C2)=$I$2:$I$6),COLUMNS($C2:C2))),"")
 
Upvote 0
That looks great and I would be happy for displaying as mutiple columns. I have attempted to put it across and have messed it up slightly due to different columns etc.
would you be able to advise for what I would put in for cells C8:G12? (excel columns and rows are marked in bold)

ABCDEFGHIJKLM
1R1R2R3R4R5R6Total
2a1111116
3b1111116
4c1111116
5d1111116
6e1111116
7
81st
92nd
10
3rd​
114th
125th

To get the duplicates in a single cell would need the TEXTJOIN function, which, unfortunately, is not available in excel 2016.

You would need to use a multiple column approach for the results as shown in the simple example below,
Book1
ABCDEFGHI
2aa 1
3bbc2
4cd 2
5de 3
6e4
Sheet10
Cell Formulas
RangeFormula
C2:D5C2=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($I$2:$I$6)/(ROWS(C$2:C2)=$I$2:$I$6),COLUMNS($C2:C2))),"")
 
Upvote 0
I'm not entirely sure what you expect in that example, I had based the suggestion on the source data having only 2 columns of data, (values to rank in column I and labels to return in column A).

Fitting the formula to your example in post 3 would mean that there is a 36 way tie for 1st, with the remaining rows being empty, which I doubt is correct?
 
Upvote 0
Apologies that's confusing. I was trying to show where my data goes in in columns B to G. There are 5 rows to rank "a" to "e" (all currently showing as tied)
i would be looking to display the "a" to "e" in cells C8:C12 although using D8:D12 if there were multiple ties
 
Upvote 0
I'm still not entirely sure that I follow, could you perhaps redo your example, with some variety in the data (some tied, some not) and type / copy and paste the results that you want to see into the bottom part?
 
Upvote 0
I would be after something like this, (I have not included the row and column markers from excel but the top left box with !! in is reference A1)
The boxes with * in could potentially display the other letters "a" to "e" for example if there was a 5 way tie all of the boxes to the right of 1st would be filled. Whereas if there were no ties, all of the boxes next to 1st, 2nd, 3rd, 4th and 5th would contain only one of each letter. i hope this is clearer

!!R1R2R3R4R5R6Total
a1111116
b23452319
c32412214
d11124514
e45364325
1st
e****
2nd
b****
3rd
cd***
4th​
*****
5tha****
 
Upvote 0
I see now, think I just missed something obvious before :eek:

It's going to need a different approach to set the last result correctly, the method that I suggested would show "a" in 4th and leave 5th empty using your example above.
Might take me a while to find something that works, but I'll try and get something posted later this evening if nobody else steps in with an answer first.
 
Upvote 0
How about:

Book1
ABCDEFGH
1R1R2R3R4R5R6Total
2a1111116
3b23452319
4c32412214
5d11124514
6e45364325
7
81ste    
92ndb    
103rdcd   
114th     
125tha    
Sheet12
Cell Formulas
RangeFormula
D8:H12D8=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($H$2:$H$6)/(COUNTIF($H$2:$H$6,">"&$H$2:$H$6)=ROWS($D$8:$D8)-1),COLUMNS($D8:D8))),"")


I'm playing around with another option, but it looks like that will be more complicated.
 
Upvote 0
Looks good, Eric :)

I've been getting nowhere with more complicated efforts, didn't think of using countif as the criteria.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top