Count/Rank

[zinah]

Hi,

I have file which has all employees with their objectives. What I need is to count how many objectives per employee, then assign number from 1 to 5 so I will end up by including up to 5 objectives per employee.

Column A (EE ID) Column B (Objectives)


Thank you!
Z

 

[mrshl9898]

Hi,

Do you mean:

[TABLE="width: 500"]
<tbody>[TR]
[TD]EE ID[/TD]
[TD]Objective
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]do 1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]do 2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]do 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]do 1[/TD]
[/TR]
</tbody>[/TABLE]


If so, =COUNTIF(A:A,A2,B:B) will count the objectives per employee.

What do you mean by assign a number?

Maybe provide an example of the data, and what result you want.

 

[zinah]

Hi, thank you for your respond.
I wish I know how to add the table here or even snapshot of what I have, but what I have is a file that has more than 1400 rows, includes employees with their yearly objectives. Some employees have more than 5 objectives and what I need is to:
- First: rank all objectives (1,2,3,...,10) with no conditions.
- Then: select only 1 to 5 objectives.
Does that make sense?

 

[mrshl9898]

Not really, you can add a table through the advanced reply option.

Or, use a sharing service like dropbox to upload the sheet, or a similar sheet with fake data if you have sensitive information.

 

[zinah]

Hi,

This is the example of what I need, I have EE ID which is employee ID and each employee has several objectives, what I need is to count how many objective for each employee (which is easy by counting how many ID for employee). However, what I need in Objective Count column is to put number of each objective, i.e. EE ID 1111 has 2, so 1 & 2, EE ID 777777 has 6 objectives, so 1 to 6.
Am I clear now?


[TABLE="width: 213"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]EE ID[/TD]
[TD]Objective[/TD]
[TD]Objectives Count[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]AAAAA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]BBBBB[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD]CCCC[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD]DDDDDDDDD[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD]FFFFFFFF[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD]EEEEEE[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD]GGGGGG[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD]TTTTTTT[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]777777[/TD]
[TD]ANNNNN[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]777777[/TD]
[TD]MMMMM[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]777777[/TD]
[TD]UUUUU[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]777777[/TD]
[TD]GUUUUU[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]777777[/TD]
[TD]OOOOOO[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]777777[/TD]
[TD]JJJJJ[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Thanks for the table.

Try =COUNTIF(A$2:A2,A2) in C2, copied down.

 

[zinah]

THANK YOU!!!! I really appreciate your time and help, it worked perfectly great!

 

[zinah]

Hi,
What if I have a new condition which count only the "Active"?

[TABLE="width: 327"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]EE ID[/TD]
[TD]Objective[/TD]
[TD]Objectives Count[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD="align: right"]11111[/TD]
[TD]AAAAA[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]11111[/TD]
[TD]BBBBB[/TD]
[TD="align: right"]2[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]33333[/TD]
[TD]CCCC[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]33333[/TD]
[TD]DDDDDDDDD[/TD]
[TD="align: right"]2[/TD]
[TD]Deleted[/TD]
[/TR]
[TR]
[TD="align: right"]33333[/TD]
[TD]FFFFFFFF[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]555555[/TD]
[TD]EEEEEE[/TD]
[TD="align: right"]1[/TD]
[TD]Deleted[/TD]
[/TR]
[TR]
[TD="align: right"]555555[/TD]
[TD]GGGGGG[/TD]
[TD="align: right"]2[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]555555[/TD]
[TD]TTTTTTT[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]777777[/TD]
[TD]ANNNNN[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]777777[/TD]
[TD]MMMMM[/TD]
[TD="align: right"]2[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]777777[/TD]
[TD]UUUUU[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]777777[/TD]
[TD]GUUUUU[/TD]
[TD="align: right"]4[/TD]
[TD]Deleted[/TD]
[/TR]
[TR]
[TD="align: right"]777777[/TD]
[TD]OOOOOO[/TD]
[TD="align: right"]5[/TD]
[TD]Deleted[/TD]
[/TR]
[TR]
[TD="align: right"]777777[/TD]
[TD]JJJJJ[/TD]
[TD="align: right"]6[/TD]
[TD]Active[/TD]
[/TR]
</tbody>[/TABLE]