Count unique values by group eliminating duplicates across groups by in Excel

0
votes

NOTE: I have already checked this count unique text values based on condition in another column between date criteria and it does not solve my problem.

I have an Excel chart with names and their groups. I want to distinct count by group with no-repetition. See Unique (Requirement) column for desired output. The purpose is to count how many people appeared first time by month.

Name    Group    Unique-(NEED THIS COLUMN)

Ryan    Jan-16   1 
Ryan    Jan-16   0 
Sam     Jan-16   1 
Sally   Feb-16   1 
Ryan    Feb-16   0 
Sam     Mar-16   0 
Tom     Mar-16   1 
Peter   Mar-16   1

Note: Although Ryan appears three times, he is only counted the first time he appears in any group. The requirement is for last Column "Unique" to have a 1 or 0 so we can create a pivot table to group by "Group" like below:

Group      Count
Jan-16     2
Feb-16     1
Mar-16     2

The names are listed only once per group by month and do not repeat even if their name appears in a subsequent month.

1
excelpivot-table

1 Answers

1
votes

If the names are starting in A2 with a heading in A1, you can just use this starting in C2 and pulled down:-

=COUNTIF(A$1:A1,A2)=0

to get true/false values, or

=--(COUNTIF(A$1:A1,A2)=0)

to get ones and zeroes.