Ranking of data based on multiple criteria

siddharthnk

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

Yes. Just adjust the ranges to suit.
 
Last edited:
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi Aladin,
I was working on my file using the formula. It worked up to a point and then I noticed inconsistencies. Is there a way I can send my file to you so that you can take a look at what is going wrong?
 
Upvote 0
Is there a way I can send my file to you so that you can take a look at what is going wrong?
Not directly, it is not permitted. Refer to #4 of the Forum Rules.

If possible, post a smallish sample that demonstrates the problem, using a table like you did in your earlier posts or one of the suggestions from Attachments. Failing that, you can upload a sample that demonstrates the problem to a file-share site and provide a link to that. Note, however, that many of the helpers here choose not to download files from other sites or, due to security restrictions at work sites, are unable to download such files.


Here is another option for you to try anyway. It doesn't require the C+S+E entry confirmation.

Excel Workbook
ABCDEFG
1YES/NOCODESSERIAL NO'S
2YES123451
3YES123462
4NO12356
5YES123451
6NO12356
7YES123573
8YES123462
9YES123462
10YES123474
11YES123474
Serial Nos
 
Last edited:
Upvote 0
Mr. Peter_SS,

Thanks a lot. Your solution worked perfectly. I have already executed and published my report. Apologies for my late response.

Nevertheless, thanks to Mr. Aladin too for all your efforts.
 
Upvote 0
Mr. Peter_SS,

Thanks a lot. Your solution worked perfectly. I have already executed and published my report. Apologies for my late response.

Nevertheless, thanks to Mr. Aladin too for all your efforts.
Cheers, glad you had a successful outcome. Thanks for letting us know.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,722
Members
448,294
Latest member
jmjmjmjmjmjm

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