Assign serial to data with multiple criteria

Hello,

I have large Excel data and need to assign incremental serial number or ranking (1, 2, 3, etc..) to a new column based on multiple criteria.

Criteria would be look at a range of data, if Part, Oper and Date matched, assign rank number starting with 1 and increment by 1 to a new column called "Rank". Notice row 3 and 4 will be assigned 1 and 2, where there is no multiple data match then, it would be 1.

I don't know if the Rank function would do the job and I have no knowledge of the function. Any help would be appreciated on how to achieve this. The sample data and result wanted are below. Thank you.

Sample data:

Part Order Oper Date
A 213 10 5/23/16
A 235 30 5/24/16
A 123 30 5/24/16
A 125 40 5/25/16
A 163 20 5/26/16
A 185 20 5/26/16
A 169 20 5/26/16
B 111 50 5/23/16
B 192 50 5/23/16
B 182 10 5/24/16
B 132 50 5/25/16
B 523 30 5/26/16

Results needed.

Part Order Oper Date Rank
A 213 10 5/23/16 1
A 235 30 5/24/16 1
A 123 30 5/24/16 2
A 125 40 5/25/16 1
A 163 20 5/26/16 1
A 185 20 5/26/16 2
A 169 20 5/26/16 3
B 111 50 5/23/16 1
B 192 50 5/23/16 2
B 182 10 5/24/16 1
B 132 50 5/25/16 1
B 523 30 5/26/16 1

I'll assume your original data is in columns A to D with labels in row 1, data beginning in row 2. The simplest way to do this would be to sort the table by Part then Date then Oper, all in ascending order. When sorted, enter 1 in cell E2 (rank for topmost row). Enter the following formula In cell E3.

=AND(A3=A2,C3=C2,D3=D2)*E2+1

Fill E3 down into as many rows as there's data in columns A to D. In E3 this means increment the rank in E2 when the Part, Oper and Date fields in rows 2 and 3 are the same; otherwise, start over with rank 1.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks for the help.

Just wondering is there a way to do it without having to sorted this way. Can it look at a range of cells instead, look for records with the same part, date and oper, then determine the ranking.

I had other formula in the data that prevented me from sorting the way you suggested.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

You could add column F to the table, start off entering =ROW() into F2, fill down (I'll assume 1,000 rows, so fill down to F1001), select F2:F1001, copy, then paste-special as values on top of itself. That gives original row numbers. Sort as I assumed above, enter the column E formulas, then select E2:E1001, copy, then paste special as values. Then resort on column F to revert to original row order, then clear column F.

If you want to do this without assuming sorting, the formulas are more complicated and can take a lot longer to recalculate. Assuming no sorting, still enter 1 in cell E2 since it'd still be the first record. Enter the following formula in E3.

=COUNTIFS(A$2:A2,A3,C$2:C2,C3,D$2:D2,D3)+1

Fill E3 down into E4:E1001.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated October 12, 2020 Views 227 Applies to: