Counting numerous entries but only identifying each entry once

[Zakky]

Hello, Mr Excel Team.
I hope someone can make my life a little easier by solving an Excel issue.
I get a report with list of employees which repeats each name several times. I need to count the total number of unique employees by placing 1 next to the name. I need number 1 because i can then do a COUNT to count the total number of employees on the list. At the moment I'm placing 1 next to each employee. Is there a better and quicker was to do it so '1' is placed as shown on the attached screenshot? The list keeps growing so need it to be dynamic so that it includes the last entry. Many thanks.

 

[Akuini]

EDIT: DELETED
wrong formula

 

[Akuini]

In Excel 365, try:

=COUNTA(UNIQUE(FILTER($B2:$B2000; $B2:$B2000<>"")))

Book1
	A	B	C
1		NAME	7
2		Malaki
3		Dominik
4		Makai
5		Marlon
6		Ridge
7		Matteo
8		Dominik
9		Makai
10		Marlon
11		Ridge
12		Abraham
13
Sheet4

Cell Formulas
Range	Formula
C1	C1	=COUNTA(UNIQUE(FILTER($B2:$B2000, $B2:$B2000<>"")))

 

[Zakky]

Akuini, thank you for the quick response. I have other data related to each employee to the left and right columns which i cannot delete. I need the '1' next to each name (preferably on the first name that appears) so it aligns with other data. For example employee ID can appear 5 items in, say, column 'A' highlighting that all data in those rows relates to that employee. This is why i can't have 5 x '1' because it will then count 5 instead of '1'. if the only data i had were names in a list then your function would work.

 

[Akuini]

Akuini, thank you for the quick response. I have other data related to each employee to the left and right columns which i cannot delete. I need the '1' next to each name (preferably on the first name that appears) so it aligns with other data. For example employee ID can appear 5 items in, say, column 'A' highlighting that all data in those rows relates to that employee. This is why i can't have 5 x '1' because it will then count 5 instead of '1'. if the only data i had were names in a list then your function would work.

I can do that with VBA but not formula.
So, hopefully somebody will be able to help you with the formula.

 

[AhoyNC]

Would this work for you?
Drag formula down as needed.

Book2
	A	B
1	Count	EMP Name
2	1	Employee A
3		Employee A
4		Employee A
5	1	Employee B
6		Employee B
7		Employee B
8		Employee B
9		Employee B
10	1	Employee C
11		Employee C
12		Employee C
13		Employee C
14	1	Employee D
15		Employee D
16	1	Employee E
17		Employee E
18	1	Employee F
19		Employee F
20		Employee F
21	1	Employee G
22		Employee G
23		Employee G
Sheet1

Cell Formulas
Range	Formula
A2:A23	A2	=IF(COUNTIF($B$2:B2,B2)=1,1,"")

 

[Zakky]

AhoyNC, magic! Exactly what I was looking for. thank you for your help.