Count uniques per column value

[anonemous]

I have a large one year dataset (around 20,000 rows) where there are two main columns, one is the unique identifier (duplicated), the other is the employee assigned to that unique identifier (unique).

I am trying to develop a formula to count the number of unique employees assigned to each identifier, as an example below. Columns A-B is the data. Columns D-E is what I want the output to look like. So far I have tried some SUM and FREQUENCY, none that seem to work seamlessly or perhaps I am making the formula too complicated for what I need to do.

Thank you very much for your knowledge.

Excel 2013/2016
	A	B	C	D	E
1	Project	Employee		Project	Employees
2	2017-0001	1411 - John		2017-0001	1
3	2017-0002	1411 - John		2017-0002	2
4	2017-0002	212 - David		2017-0003	1
5	2017-0003	1411 - John		2017-0004	1
6	2017-0004	1411 - John		2017-0005	1
7	2017-0005	1411 - John		2017-0006	1
8	2017-0006	1411 - John		2017-0007	1
9	2017-0007	1411 - John		2017-0008	1
10	2017-0008	1411 - John		2017-0009	3
11	2017-0009	1411 - John		2017-0010	1
12	2017-0009	212 - David		2017-0011	1
13	2017-0009	344 - Jason		2017-0012	1
14	2017-0010	1411 - John		2017-0013	1
15	2017-0011	1411 - John		2017-0014	1
16	2017-0012	1411 - John		2017-0015	2
17	2017-0013	1411 - John
18	2017-0014	1411 - John
19	2017-0015	1411 - John
20	2017-0015	3773 - Charles
Data Sheet

 

[rpaulson]

if i undertand correctly then, this should work in cell E2
=COUNTIF(A:A,D2)
fill down as needed

hth,
Ross

 

[AhoyNC]

Try:
Assumes there are no blank columns in the Employee field.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down rows as needed.

Excel Workbook
	A	B	C	D	E
1	Project	Employee		Project	Employees
2	2017-0001	1411 - John		2017-0001	1
3	2017-0002	1411 - John		2017-0002	2
4	2017-0002	212 - David		2017-0003	1
5	2017-0003	1411 - John		2017-0004	1
6	2017-0004	1411 - John		2017-0005	1
7	2017-0005	1411 - John		2017-0006	1
8	2017-0006	1411 - John		2017-0007	1
9	2017-0007	1411 - John		2017-0008	1
10	2017-0008	1411 - John		2017-0009	3
11	2017-0009	1411 - John		2017-0010	1
12	2017-0009	212 - David		2017-0011	1
13	2017-0009	344 - Jason		2017-0012	1
14	2017-0010	1411 - John		2017-0013	1
15	2017-0011	1411 - John		2017-0014	1
16	2017-0012	1411 - John		2017-0015	2
17	2017-0013	1411 - John
18	2017-0014	1411 - John
19	2017-0015	1411 - John
20	2017-0015	3773 - Charles
Sheet

 

[Aladin Akyurek]

In E2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$20=$D2,IF(1-($B$2:$B$20=""),MATCH($B$2:$B$20,$B$2:$B$20,0))),ROW($B$2:$B$20)-ROW($B$2)+1),1))

 

[AhoyNC]

@Aladin - in your formula is there an advantage to using 1-($B$2:$B$20="") vs $B$2:$B$20 < > ""

 

[anonemous]

Thank you, @rpaulson @AhoyNC @Aladin Akyurek
All three formulas worked in the data sheet - Over hindsight, I did not realize it was possible to do @rpaulson 's method without reading the field with employee names, but just count for duplicated entries. It can be made to work since since all row possibilities can be deduped to uniques (e.g. no two rows were the same).

@AhoyNC and @Aladin Akyurek methods read through all the data, looked at duplicates and generated the count of employees based on the actual sum of matches per each identifier. This process took some time for the machine to parse the formula (not quick with ~20K rows!)

I have learned new formulas from this topic and appreciate the insight it has given me. Thank you, kind benefactors.

 

[AhoyNC]

You're welcome the formulas that Aladin & I gave would take into account if an employee name was duplicated on a project. Aladin's also took into account if there was a blank cell.

 

[Aladin Akyurek]

@Aladin - in your formula is there an advantage to using 1-($B$2:$B$20="") vs $B$2:$B$20 < > ""

No.

 

[Aladin Akyurek]

[...]
Over hindsight, I did not realize it was possible to do @rpaulson 's method without reading the field with employee names, but just count for duplicated entries. It can be made to work since since all row possibilities can be deduped to uniques (e.g. no two rows were the same).
[...]

If no employee is associated with a project more than once, the COUNTIF solution is good enough...