count unique for each

[deb]

excel 2010


I need to count the unique ProjectName (column B) for each ProjectID (column A) and enter it in column C

i.e. MD has 1 unique name, BW has 2 unique names...

.........A...........B..........C
1......MD.......McDan.....1
2......MD.......McDan.....1
3......BW.......Bill..........2
4......AF........Alf...........1
5......BW.......Billie........2

 

[Aladin Akyurek]

Sorry
MD is ProjectID McDan is the ProjectName (Name)

Bill doesn't look like a project name.

I assume you want to count how many people works/are associated with a project id. Moreover you want this over two sheets of which NCC is one. Right?

 

[deb]

That is correct

2 sheets. counting unique ProjectNames (aka Names) for each ProjectID.

 

[Aladin Akyurek]

That is correct

2 sheets. counting unique ProjectNames (aka Names) for each ProjectID.

Care to state the exact sheet names, the ranges involved, and where you want to collect the results?

 

[deb]

Sheet1 Named NCC, column F ProjectNames, column E ProjectID
Sheet 2 Named Revenue, column E ProjectNames, column D ProjectID

Result should go in Sheet1 Named NCC, column S called ProjectNameMisspelled

Thank you for your help. this is important to the data and I wish I knew enough to figure it out.
At first I though the one sheet would be enough and then I found wrong names on both sheets and they need to be consistant.

 

[Aladin Akyurek]

Sheet1 Named NCC, column F ProjectNames, column E ProjectID
Sheet 2 Named Revenue, column E ProjectNames, column D ProjectID

Result should go in Sheet1 Named NCC, column S called ProjectNameMisspelled

Thank you for your help. this is important to the data and I wish I knew enough to figure it out.
At first I though the one sheet would be enough and then I found wrong names on both sheets and they need to be consistant.

Given your samples...

Row\Col	E​	F​	S​
1​	sheet:	NCC
2​	Project ID	ProjectName	ProjectNameMisspelled
3​	MD	McDan
4​	MD	McDan
5​	BW	Bill
6​	AF	Alf
7​	BW	Billie

Row\Col	D​	E​
1​	sheet:	Revenue
2​	Project ID	Project Name
3​	BW	Bob
4​	AF	Alfred

What are the results that you want to see under the header ProjectNameMisspelled?

 

[deb]

With your previous examples, I was able to figure it out....

=IF(COUNTIFS(E:E,"<>"&E2,D:D,D2)+COUNTIFS(NCC!F:F,"<>"&E2,NCC!E:E,D2)>0,"Verify Project names","")

Thank you!!