| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locate unique items
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. -- Thanks, MarkN |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locate unique items
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. "MarkN" wrote: 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. -- Thanks, MarkN |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locate unique items
DataFilterAdvanced Filter. It has a Unique records option.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "MarkN" wrote in message ... 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. -- Thanks, MarkN |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locate unique items
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 "Bob Phillips" wrote: DataFilterAdvanced Filter. It has a Unique records option. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "MarkN" wrote in message ... 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. -- Thanks, MarkN |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locate unique items
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) "MarkN" wrote in message ... 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 "Bob Phillips" wrote: DataFilterAdvanced Filter. It has a Unique records option. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "MarkN" wrote in message ... 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. -- Thanks, MarkN |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locate unique items
Thanks for this Bob, it works well
-- Thanks, MarkN "Bob Phillips" wrote: 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) "MarkN" wrote in message ... 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 "Bob Phillips" wrote: DataFilterAdvanced Filter. It has a Unique records option. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "MarkN" wrote in message ... 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. -- Thanks, MarkN |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set up a formula to count only unique items in a column? | Excel Worksheet Functions | |||
| Setting up a validation of data listbox to provide the unique items within a range | Excel Worksheet Functions | |||
| How do I set up a formula to count only unique items in a column? | Excel Worksheet Functions | |||
| create an array with unique items IN MEMORY | Excel Worksheet Functions | |||
| Count of unique items meeting condition | Excel Worksheet Functions | |||
Linear Mode
