Simon Lloyd
Administrator
- Joined
- Apr 2, 2011
- Messages
- 401
- Reaction score
- 0
- Points
- 0
- Location
- Manchester, England
- Excel Version(s)
- Excel 2016
Hi all, this isn't a question for help but more a discussion. At many forums we're constantly asked "How can i see the unique values in a list?", yep, that spang to mind for me too! (what? you weren't thinking of Data>Advanced Filter>Filter Unique????, shame on you) and that would be my stock answer, however i find that i am more and more advocating the use of a Pivot Table because of its power and versatility, yet when giving this advice i almost always get back "....but i need a formula!", ok i can understand the need to see the data in situ with everything else but it has it's draw backs.
Here's a stock formula for display unique values from column A:
Use an Array formula:
{=INDEX($A$2:$A$8,MATCH(0,COUNTIF($B$2:B2,$A$2:$A$8),0))}
Drawbacks?, the committing of the formula requires the 3 simutaneously pressed keystroke, the committed array formula breaks when clicking in to the cell and of course its volatile, all of these are the reasons for my reluctance to give that solution.
What thoughts do you have on this? got any better ways?
Here's a stock formula for display unique values from column A:
Use an Array formula:
{=INDEX($A$2:$A$8,MATCH(0,COUNTIF($B$2:B2,$A$2:$A$8),0))}
Drawbacks?, the committing of the formula requires the 3 simutaneously pressed keystroke, the committed array formula breaks when clicking in to the cell and of course its volatile, all of these are the reasons for my reluctance to give that solution.
What thoughts do you have on this? got any better ways?