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?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
Here's the other option I mentioned:

Book1
ABCDEFGH
1R1R2R3R4R5R6Total
2a1111116
3b23452319
4c32412214
5d11124514
6e45364325
7
8
9RankScoreName
101st25e
112nd19b
123rd (tie)14c
133rd (tie)14d
145th6a
Sheet12
Cell Formulas
RangeFormula
B10:B14B10=IF(C10=C9,B9,CHOOSE(ROWS(C$10:C10),"1st","2nd","3rd","4th","5th")&IF(COUNTIF(C$10:C$14,C10)>1," (tie)",""))
C10:C14C10=LARGE($H$2:$H$6,ROWS($C$10:$C10))
D10:D14D10=INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$6)/(H$2:H$6=C10),COUNTIF(C$10:C10,C10)))


It turned out to be not too difficult, although I added the score column. Removing it and still keeping the gist of the other 2 columns is possible, but it surprisingly ramps up the complications a lot.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,978
Office Version
  1. 365
Platform
  1. Windows
Nice, not 100% what the OP was asking for but an interesting approach to the task.

Something that crossed my mind when looking at your suggestion was using conditional formatting (white text) with the ranks entered into the cells as the OP started with rather than using a formula.
Book1
B
9Rank
101st
112nd
123rd
134th
145th
Sheet11
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B10:B14Expression=C10=C9textNO
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
Nice, not 100% what the OP was asking for but an interesting approach to the task.

True enough, I'm just throwing out options.

Something that crossed my mind when looking at your suggestion was using conditional formatting (white text) with the ranks entered into the cells as the OP started with rather than using a formula.

Good thought. In case the background isn't white, you could also use a custom number format of ;;; to hide the text.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,978
Office Version
  1. 365
Platform
  1. Windows
I'm just throwing out options.
Personally, I think it is the better option (y)

Good call on using ;;; for the formatting, I've used that many times (suggested it in another thread earlier today) but for whatever reason it didn't come to mind here.

Maybe I'm over qualified in the art of inconsistency 😱
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Personally, I think it is the better option (y)

Good call on using ;;; for the formatting, I've used that many times (suggested it in another thread earlier today) but for whatever reason it didn't come to mind here.

Maybe I'm over qualified in the art of inconsistency 😱

@Eric W @jasonb75

Because of you guys I have started learning excel. I am digging out your replies to threat and they are blowing my mind.

And @jasonb75 even your inconsistency is helping us to think.

Thanks
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
I Have tried to solve but i am getting the duplicate in 4th row as well. How to remove the value from 4th row.
@Eric W

Book1
ABCDEFGH
1R1R2R3R4R5R6Total
2a1111116
3b23452319
4c32412214
5d11124514
6e45364325
7
8ERIC W Formula
91ste    
102ndb    
113rdcd   
124th     
135tha    
14
15Punit Formula
161e     
172b     
183dc    
194d     
205a     
21
Sheet2
Cell Formulas
RangeFormula
D9:H13D9=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($H$2:$H$6)/(COUNTIF($H$2:$H$6,">"&$H$2:$H$6)=ROWS($D$9:$D9)-1),COLUMNS($D9:D9))),"")
D18,C16:C20C16=INDEX($A$2:$A$6,AGGREGATE(14,6,1/(1/IF(AGGREGATE(14,6,$H$2:$H$6,$B16)=$H$2:$H$6,ROW($1:$5))),COLUMNS($K$16:K16)))
D16:H17,E18:H18,D19:H20D16=IFERROR(INDEX($A$2:$A$6,AGGREGATE(14,6,1/(1/IF(AGGREGATE(14,6,$H$2:$H$6,C16)=$H$2:$H$6,ROW($1:$5))),COLUMNS($K$16:L16))),"")
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,978
Office Version
  1. 365
Platform
  1. Windows
Your formulas are as inconsistent as my methods ;) I would say that the formula in C16:C20 and D18 is the correct one, it looks like you broke something when you added IFERROR to it.

Check the k value in aggregate, C16?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,242
Messages
5,600,506
Members
414,385
Latest member
Lioness227

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
Top