Identify Unique Items

G

Guest

Consider the list below. I want to somehow count the number of unique
assignments for each manager.

Manager Assignments
Fred Neil
Joe Ian
Joe Dave
Fred Alan
Fred Rick
Joe Dave
Fred Neil
Joe Andrew
Fred Rick
Fred Alan
Fred Rick
Joe Dave
Joe Neil
Fred Alan
Fred Dave
Joe Neil

e.g. using filter, Fred has 9 assignments, but some are duplicated, and so
has only 4 unique assignments. How can I determine this number is 4? Joe has
7 assignments, but only 4 unique assignments.

This is a much simplified example where in reality there are 20 managers
each of which may have 60 unique assignments.

Is this where a pivot table should be used?

Any advice would be gratefully received.

Thanks.
 
B

Bob Phillips

Pivot tables sound best, but if you want a formula

=SUM(--(FREQUENCY(IF(A2:A100="Bob",MATCH(B2:B100,B2:B100,0)),ROW(INDIRECT("1
:"&ROWS(B2:B100))))>0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Guest

Bob -

Thanks for the tip - that really helps me.

I am glad for the formula as I am a bit of dunce when it comes to Pivot
tables. Any pointers?

What is the significance of the double minus sign in the formula?

Many thanks
Neil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count unique with multiple criteria 3
Formula to count the number of unique dates 7
rank question 1
Exporting Query to Excel - Multiple Files 4
Pivot Tables - Unique items 2
count if unique entries 3
Please, need help with multiple "if" conditions 5
More Help with Unique Values 1

Top