Rank duplicate numbers

O

ORoxo

Board Regular
Joined
Oct 30, 2016
Messages
149
Hello again, guys
I'm using the following formula to rank Risks according to the value in a cell. However, right now, the formula ignores duplicates.

Let's say I have the same risk listed twice - which can happen. The formula will just ignore this and assign it the next available number. I was hoping there's some way for it take into consideration the previous number assigned to a specific risk.
This is a sample and the expected result:

Capture.png



Thanks!
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Maybe:


ABCDE
RiskValueAreaExpected Result
RP1AAA
RP1
RP2BBB
RP3CCC
RP4DDD
RP5DDD

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]73[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]73[/TD]
[TD="align: right"]AAA[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]57[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]19[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]39[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]56[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IF($B$2:$B$8>B2,MATCH($A$2:$A$8,$A$2:$A$8,0)),ROW($A$2:$A$8)-ROW($A$2)+1)))+1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Eric W said:
Maybe:


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Risk[/TD]
[TD]Value[/TD]
[TD]Area[/TD]
[TD]Expected Result[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]RP1[/TD]
[TD="align: right"]73[/TD]
[TD]AAA[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]RP1[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]AAA[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]RP2[/TD]
[TD="align: right"]57[/TD]
[TD]BBB[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]RP3[/TD]
[TD="align: right"]19[/TD]
[TD]CCC[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]RP4[/TD]
[TD="align: right"]39[/TD]
[TD]DDD[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]RP5[/TD]
[TD="align: right"]56[/TD]
[TD]DDD[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IF($B$2:$B$8>B2,MATCH($A$2:$A$8,$A$2:$A$8,0)),ROW($A$2:$A$8)-ROW($A$2)+1)))+1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

This is not exactly what I am looking for although it works for that sample, I'm sorry. I will try to make use of a better example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Risk[/TD]
[TD]Value[/TD]
[TD]Department[/TD]
[TD]Expected Value[/TD]
[/TR]
[TR]
[TD]RA11[/TD]
[TD]22[/TD]
[TD]A&T[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]RA11[/TD]
[TD]22[/TD]
[TD]A&T[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]RA44[/TD]
[TD]22[/TD]
[TD]A&T[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]RA45[/TD]
[TD]22[/TD]
[TD]A&T[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]RA46[/TD]
[TD]22[/TD]
[TD]A&T[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]RC8[/TD]
[TD]27,5[/TD]
[TD]COM[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]RC8[/TD]
[TD]27,5[/TD]
[TD]COM[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]RC8[/TD]
[TD]27,5[/TD]
[TD]COM[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]RC8[/TD]
[TD]27,5[/TD]
[TD]COM[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]RC34[/TD]
[TD]22[/TD]
[TD]COM[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


Basically, I would like the formula to have into consideration department and the number of the risk. It should rank same for the same risk, regardless of how many times it appears and it should rank them at a department level only.

Thanks once again for the support
 
Last edited:
Upvote 0
Try:

Excel 2012
ABCD
RiskValueDepartmentExpected Value
RA11A&T
RA11A&T
RA44A&T
RA45A&T
RA46A&T
RC8COM
RC8COM
RC8COM
RC8COM
RC34COM

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]27,5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]27,5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]27,5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]27,5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]2[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IF($C$2:$C$11=C2,IF(MID($A$2:$A$11,3,9)+0<MID(A2,3,9)+0,MATCH($A$2:$A$11,$A$2:$A$11,0))),ROW($A$2:$A$11)-ROW($A$2)+1)))+1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Using column A for the rating is a bit problematic. I assumed that there is a 2-character prefix, and then the rest of the value is the rating.
 
Upvote 0
Eric W said:
Try:

Excel 2012
ABCD
RiskValueDepartmentExpected Value
RA11A&T
RA11A&T
RA44A&T
RA45A&T
RA46A&T
RC8COM
RC8COM
RC8COM
RC8COM
RC34COM

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]27,5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]27,5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]27,5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]27,5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]2[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IF($C$2:$C$11=C2,IF(MID($A$2:$A$11,3,9)+0<mid(<font color="#FF00FF">A2</mid(<font color="Blue"><font color="Red"><mid(,3,9</mid()+0,MATCH($A$2:$A$11,$A$2:$A$11,0))),ROW($A$2:$A$11)-ROW($A$2)+1)))+1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Using column A for the rating is a bit problematic. I assumed that there is a 2-character prefix, and then the rest of the value is the rating.
Click to expand...

Eric, thanks again fr your help! Could you please check the part in red bold on your formula?

Thanks
 
Upvote 0
Ugh! Sorry, I still get caught occasionally by a quirk where the forum interprets a < as an HTML tag and not as an operator:

Code: 
=SUM(SIGN(FREQUENCY(IF($C$2:$C$11=C2,IF(MID($A$2:$A$11,3,9)+0< MID(A2,3,9)+0,MATCH($A$2:$A$11,$A$2:$A$11,0))),ROW($A$2:$A$11)-ROW($A$2)+1)))+1
 
Upvote 0
Eric W said:
Ugh! Sorry, I still get caught occasionally by a quirk where the forum interprets a < as an HTML tag and not as an operator:

Code: 
=SUM(SIGN(FREQUENCY(IF($C$2:$C$11=C2,IF(MID($A$2:$A$11,3,9)+0< MID(A2,3,9)+0,MATCH($A$2:$A$11,$A$2:$A$11,0))),ROW($A$2:$A$11)-ROW($A$2)+1)))+1
Click to expand...

Thanks, Eric! This does work perfectly. I would just like it to take int consideration column B. Would that be possible?
 
Upvote 0
In what way do you want to consider column B? As part of the rating (if so, how), or as part of the department grouping (again, if so, how)?
 
Upvote 0
Eric W said:
In what way do you want to consider column B? As part of the rating (if so, how), or as part of the department grouping (again, if so, how)?
Click to expand...

I want it to consider it as part of the ranking.

In other words, I want to rank the risks according to the value in column B and within each department, that's the general rule. Let's say, however, that I have two rows in my dataset with the same Risk Name. In that case, I want it to give it the same rank.

If it's not yet clear let me know, I will try to explain it in another way.
 
Upvote 0
I don't think I quite picture it. Could you post an example? And could you verify that the risk ranking is based on column A, without the first 2 characters?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
406
Fluff
Fluff
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
92
Pingaware
P
S
  • Question Question
Unexpected and inconsistent error with ranking rows based on criteria
Replies
2
Views
361
strugglingaccountant
S
L
  • Question Question
Correct mistaken info
Replies
5
Views
366
abdelfattah
A
J
  • Question Question
Excel Formula Inquiry - Numbers to determine duplicate
Replies
8
Views
341
awoohaw
awoohaw

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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