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:

EE ID	Objective
1	do 1
1	do 2
1	do 3
2	do 1

<tbody>
</tbody>

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?

EE ID	Objective	Objectives Count
11111	AAAAA	1
11111	BBBBB	2
33333	CCCC	1
33333	DDDDDDDDD	2
33333	FFFFFFFF	3
555555	EEEEEE	1
555555	GGGGGG	2
555555	TTTTTTT	3
777777	ANNNNN	1
777777	MMMMM	2
777777	UUUUU	3
777777	GUUUUU	4
777777	OOOOOO	5
777777	JJJJJ	6

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[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"?

EE ID	Objective	Objectives Count	Status
11111	AAAAA	1	Active
11111	BBBBB	2	Active
33333	CCCC	1	Active
33333	DDDDDDDDD	2	Deleted
33333	FFFFFFFF	3	Active
555555	EEEEEE	1	Deleted
555555	GGGGGG	2	Active
555555	TTTTTTT	3	Active
777777	ANNNNN	1	Active
777777	MMMMM	2	Active
777777	UUUUU	3	Active
777777	GUUUUU	4	Deleted
777777	OOOOOO	5	Deleted
777777	JJJJJ	6	Active

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>