Locate unique items

[MarkN]

Column A lists Clients, Column P lists Yes/No against the client name. Some
clients are listed more than once and some clients may have either yes or no
against their name:

John Yes
John No
John Yes
Dave No
Jane Yes

I somehow need to extract a unique list of clients where each of their
respective records is Yes. So, in the example above, I would want two records
returned, John and Jane.

Any ideas or suggestions welcome because there are a lot of records.

 

[DKS]

More sexy solutions will come in from other readers, but in the meantime here
is a quick and dirty solution.

Based on YES/NO, filter out the entries with Yes. copy them to another
location.

Based on new list of only YES entries, you can use the DATA / FILTER /
ADVANCED FILTER option to filter out only "unique records". The dialog box
has a checkbox at the end which must be activated to filter unique records.

This should give you the list. Of course this will work only one time. If
your need is to do this regularly then you may have to program it (with or
without above approach).

ciao.

 

[Bob Phillips]

Data>Filter>Advanced Filter. It has a Unique records option.

--
HTH

Bob Phillips

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

 

[MarkN]

Thanks for taking the time but I wasn't clear about what I wanted so I'll try
again:

John Yes
John No
John Yes
Dave Yes
Dave Yes
Dave Yes
Steve Yes
Jane No
Ian Yes
Ian No

I want to return unique names where there is a yes against every occurrence
of that name.

In this example, Dave and Steve would be returned.

--
Thanks again,
MarkN

Data>Filter>Advanced Filter. It has a Unique records option.

--
HTH

Bob Phillips

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

 

[Bob Phillips]

If the columns have header fields, you could still use Advanced Filter with
a criteria of Status and Yes.

An alternative is to build the list dynamically.

Select a range in E1:En big enough to accommodate all Yes's and in the
formula bar enter

=IF(ISERROR(SMALL(IF(B1:B20="Yes",ROW($A1:$A20),""),ROW($A1:$A20))),"",
INDEX($A$1:$A$20,SMALL(IF($B1:$B20="Yes",ROW($A1:$A20),""),ROW($A1:$A20))))

as an array formula, that is commit with Ctrl-Shift-Enter, not just Enter.

Then in F1, enter

=E1

In F2, enter

=IF(ISERROR(MATCH(0,COUNTIF(F$1:F1,$E$1:$E$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$E$20),"",$E$1:$E$20),MATCH(0,COUNTIF(F$1:F1,$E$1:$E$2
0&""),0)))

which again is an array formula, then copy down as far as you need

--
HTH

Bob Phillips

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

Thanks for taking the time but I wasn't clear about what I wanted so I'll try
again:

John Yes
John No
John Yes
Dave Yes
Dave Yes
Dave Yes
Steve Yes
Jane No
Ian Yes
Ian No

I want to return unique names where there is a yes against every occurrence
of that name.

In this example, Dave and Steve would be returned.

 

[MarkN]

Thanks for this Bob, it works well