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?
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,986
Office Version
  1. 365
Platform
  1. Windows
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))),"")
 
L

Legacy 461286

Guest
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))),"")
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,986
Office Version
  1. 365
Platform
  1. Windows
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?
 
L

Legacy 461286

Guest

ADVERTISEMENT

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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,986
Office Version
  1. 365
Platform
  1. Windows
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?
 
L

Legacy 461286

Guest

ADVERTISEMENT

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****
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,986
Office Version
  1. 365
Platform
  1. Windows
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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,986
Office Version
  1. 365
Platform
  1. Windows
Looks good, Eric :)

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

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

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