Ranking of data based on multiple criteria

siddharthnk

New Member
Joined
Jun 20, 2013
Messages
44
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.

YES/NOCODESSERIAL NO'S
YES123451
YES123462
NO12356
YES123451
NO12356
YES123573
YES123462
YES123462
YES123474
YES123474

<tbody>
</tbody>

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.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,209
Please explain how the serial no are related.

I don't see the concept.
 

siddharthnk

New Member
Joined
Jun 20, 2013
Messages
44
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

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

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,209
Code:
Yeah, but why is 12357 ranked 3, and 12347 ranked 4 [COLOR="#B22222"](twice)[/COLOR]?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
In C2 control+shift+enter, not just enter, and copy down:
Rich (BB code):
=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

New Member
Joined
Jun 20, 2013
Messages
44
Hi Aladin,

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

YES/NOCODES
YES123451
YES123562
NO13456
YES123451
NO13456
YES123562
YES123574
NO13456
NO13456
YES123585

<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
Hi Aladin,

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

YES/NOCODES
YES123451
YES123562
NO13456
YES123451
NO13456
YES123562
YES123574
NO13456
NO13456
YES123585

<tbody>
</tbody>
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

New Member
Joined
Jun 20, 2013
Messages
44
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,225
Messages
5,443,194
Members
405,219
Latest member
CraneS

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top