Ranking of data based on multiple criteria

[siddharthnk]

Hi All,

I need help with an excel formula which will allocate serial numbers a set of numbers in a column. The data is as follows.

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64"]YES/NO[/TD]
[TD="class: xl63, width: 64"]CODES[/TD]
[TD="class: xl63, width: 64"]SERIAL NO'S[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]YES[/TD]
[TD="class: xl63, width: 64"]12345[/TD]
[TD="class: xl63, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]YES[/TD]
[TD="class: xl63, width: 64"]12346[/TD]
[TD="class: xl63, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]NO[/TD]
[TD="class: xl63, width: 64"]12356[/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]YES[/TD]
[TD="class: xl63, width: 64"]12345[/TD]
[TD="class: xl63, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]NO[/TD]
[TD="class: xl63, width: 64"]12356[/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]YES[/TD]
[TD="class: xl63, width: 64"]12357[/TD]
[TD="class: xl63, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]YES[/TD]
[TD="class: xl63, width: 64"]12346[/TD]
[TD="class: xl63, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]YES[/TD]
[TD="class: xl63, width: 64"]12346[/TD]
[TD="class: xl63, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]YES[/TD]
[TD="class: xl63, width: 64"]12347[/TD]
[TD="class: xl63, width: 64"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]YES[/TD]
[TD="class: xl63, width: 64"]12347[/TD]
[TD="class: xl63, width: 64"]4[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, the first column in a yes/no column which has only yes's and no's which are formulated based on certain other criteria based on information present in other cells in the same row. The second column consists of project codes.
What I need is a formula which will allocate serial numbers to the third column based on the combination of yes's and the codes. As you can see in the screenshot, any code which is repeated with a yes has the same serial number has the same serial number. The rows where there is a no in the first column needs to be blank. Also, the codes will not be sorted in ascending or descending order and will keep increasing every week.

 

[Oeldere]

Please explain how the serial no are related.

I don't see the concept.

 

[siddharthnk]

The serial numbers or the rank are related such that every time the same code appears with a yes, it should have the same rank / serial number. A particular code can appear with a "no" but as I said earlier it needs to be ignored and has to be blank.

 

[jtakw]

Hi,

Yeah, but why is 12357 ranked 3, and 12347 ranked 4?

 

[Oeldere]

Yeah, but why is 12357 ranked 3, and 12347 ranked 4 [COLOR="#B22222"](twice)[/COLOR]?

 

[siddharthnk]

Yes you are right, my bad. It should be the other way around.

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, and copy down:

=IF($A2="Yes",IF(ISNUMBER(MATCH(B2,$B$1:B1,0)),VLOOKUP(B2,$B$1:C1,2,0),
    SUM(IF(FREQUENCY(IF($A$2:A2="yes",$B$2:B2),$B$2:B2),1))),"")

 

[siddharthnk]

Hi Aladin,

Thanks for the formula. When I apply it, I get a 4 after 2. The 3rd rank is missing.

[TABLE="width: 192"]
<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]YES/NO[/TD]
[TD="class: xl63, width: 64"]CODES[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12345[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12356[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]NO[/TD]
[TD="class: xl63"]13456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12345[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]NO[/TD]
[TD="class: xl63"]13456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12356[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12357[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]NO[/TD]
[TD="class: xl63"]13456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]NO[/TD]
[TD="class: xl63"]13456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12358[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Hi Aladin,

Thanks for the formula. When I apply it, I get a 4 after 2. The 3rd rank is missing.

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64"]YES/NO[/TD]
[TD="class: xl63, width: 64"]CODES[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12345[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12356[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]NO[/TD]
[TD="class: xl63"]13456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12345[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]NO[/TD]
[TD="class: xl63"]13456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12356[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12357[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]NO[/TD]
[TD="class: xl63"]13456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]NO[/TD]
[TD="class: xl63"]13456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]YES[/TD]
[TD="class: xl63"]12358[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

Control+shift+enter means press down the control and the shift keys at the same time while you the enter key. When done properly, Excel itself puts a pair of { and } around the formula.

 

[siddharthnk]

Thanks Aladin. The formula works! What if the colums are next to each other. I never mentioned the column names because they aren't next to each other. There might be a couple of columns between the yes/no column and code column. Does the formula work the same?