Identify Unique Items

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neil Perry - 19 Sep 2006 13:53 GMT

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.

Reply to this Message

Bob Phillips - 19 Sep 2006 13:58 GMT

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.

Signature

HTH

Bob Phillips

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

> Consider the list below. I want to somehow count the number of unique
> assignments for each manager.
[quoted text clipped - 29 lines]
>
> Thanks.

Reply to this Message

Neil Perry - 25 Sep 2006 11:02 GMT

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

> Pivot tables sound best, but if you want a formula
>
[quoted text clipped - 38 lines]
> >
> > Thanks.

Reply to this Message

Bob Phillips - 25 Sep 2006 11:08 GMT

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.

Signature

HTH

Bob Phillips

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

> Bob -
>
[quoted text clipped - 9 lines]
>
> > 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))
> >
[quoted text clipped - 35 lines]
> > >
> > > Thanks.

Reply to this Message

Bob Phillips - 25 Sep 2006 11:08 GMT

Oh yes, pivot tables.

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

Signature

HTH

Bob Phillips

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

> Bob -
>
[quoted text clipped - 9 lines]
>
> > 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))
> >
[quoted text clipped - 35 lines]
> > >
> > > Thanks.

Reply to this Message

Rate this thread: