Identify Unique Items

[Neil Perry]

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.

 

[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)

 

[Neil Perry]

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

 

[Bob Phillips]

The -- is a double unary, which coerces True/False values to 1/0. Although
this page is aimed at SUMPRODUCT, it explains that more
http://www.xldynamic.com/source/xld.SUMPRODUCT.html.

--
HTH

Bob Phillips

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

 

[Bob Phillips]

Oh yes, pivot tables.

See http://www.contextures.com/xlPivot01.html
and http://www.peltiertech.com/Excel/Pivots/pivottables.htm


--
HTH

Bob Phillips

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