| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula for unique entries
Dear Experts,
I know how to filter a list of data for unique entries using the advanced filter however I want to have it happen without user intervention. Can I use an array formula to pick out unique entries from a predetermined list and display them in adjacent columns? regards Martina |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formula for unique entries
You could put this in a helper column next to your list and drag down.
=IF(COUNTIF($A$1:$A$100,A1)=1,A1,"") Unique items will be listed in the helper column Mike "Martina" wrote: Dear Experts, I know how to filter a list of data for unique entries using the advanced filter however I want to have it happen without user intervention. Can I use an array formula to pick out unique entries from a predetermined list and display them in adjacent columns? regards Martina |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formula for unique entries
Use DataFilterAdvanced Filter, it has a uniques option.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Martina" wrote in message ... Dear Experts, I know how to filter a list of data for unique entries using the advanced filter however I want to have it happen without user intervention. Can I use an array formula to pick out unique entries from a predetermined list and display them in adjacent columns? regards Martina |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formula for unique entries
The John Walkenbach method:
=INDEX($A$1:$A$100,SMALL(IF(MATCH($A$1:$A$100,$A$1 :$A $100,0)=ROW(INDIRECT("1:"&ROWS($A$1:$A$100))),MATC H($A$1:$A$100,$A$1:$A $100,0),""),ROW(INDIRECT("1:"&ROWS($A$1:$A$100)))) ) In this case you're working with the range $A$1:$A$100, whereas generally you would have an expanding named range and a bit of error checking. On Aug 10, 4:11 am, "Bob Phillips" wrote: Use DataFilterAdvanced Filter, it has a uniques option. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Martina" wrote in message ... Dear Experts, I know how to filter a list of data for unique entries using the advanced filter however I want to have it happen without user intervention. Can I use an array formula to pick out unique entries from a predetermined list and display them in adjacent columns? regards Martina- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formula for unique entries
"Martina" wrote: Dear Experts, I know how to filter a list of data for unique entries using the advanced filter however I want to have it happen without user intervention. Can I use an array formula to pick out unique entries from a predetermined list and display them in adjacent columns? regards Martina Thanks everyone for your help. My sheet is working now. regards Martina |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array formula for unique values | Excel Worksheet Functions | |||
| Array formula for unique values | Excel Worksheet Functions | |||
| Unique Entries | Excel Worksheet Functions | |||
| Unique Entries | Excel Worksheet Functions | |||
| How to validate data entries to be unique within an array | Excel Worksheet Functions | |||
Linear Mode
