Displaying Unique values from a list

[Simon Lloyd]

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?

[JeffreyWeir]

As per the comments at http://chandoo.org/wp/2010/09/27/rem...-pivot-tables/ It’s worth pointing out that the pivot table approach might significantly bloat the size of a file, because ALL your data is duplicated within the pivot cache…which could be many many megabytes even if your pivot summary only has a few items.

So you might want to have your data stored in another workbook, and just point the pivottable to that workbook. But this will still pull all the data in the entire table through, even though you only want a distinct list of items.



So you could just pull in the column you are interested in (i.e. your ‘product name’ column), and not all the data from the entire table. But even this will pull through many duplicate items, even though your pivot table will only be displaying a list of the distinct items.

Another approach is to use a ‘Select Distinct’ or a 'group by' SQL query from microsoft query to just returns a list of distinct (i.e. non-duplicate) names , and nothing else. This is pretty easy to set up using Microsoft Query when you know how…you just have to make sure that your raw data table is in a form that MS Query can see it...although it precludes you having your list in the same spreadsheet due to memory leak issues (see the comments at http://www.excelhero.com/blog/2010/0...se-lookup.html )

[Jon von der Heyden]

Hi Simon

You can use the LOOKUP function which does not need to be CSE entered. The downside of this particular formula is that it will return the last distinct value from the range, so if you wanted to keep the order it will effectively be reversed.

Assuming field names in row 1, with values in A2:A10:

C2: =LOOKUP(2,1/(ISERROR(MATCH($A$2:$A$10,$C$1:$C1,0))),$A$2:$A$10)

Also, the formula you posted is not volatile. In fact it is the version I tend to use when a formula method is called for. Otherwise I use advanced filter. I have also used ADO in other projects when querying external databases.

[Ken Puls]

I know this isn't a formula, but most of my users are happy just looking at the Autofilter list on a table.

[Simon Lloyd]

I know this isn't a formula, but most of my users are happy just looking at the Autofilter list on a table.

It's nice not to have such demanding users

[Jon von der Heyden]

Just a few bits to add the the discussion.

I suppose another way to overcome the CSE issue with Simon's original formula would be to use it in a defined name, which will treat it as an array formula but does not need to be CSE entered.

I also like this version which can be used to return a distinct list, but return it ordered ascending:
=INDEX($A$2:$A$10,MATCH(0,COUNTIF($A$2:$A$10,"<"&$A$2:$A$10)-SUM(COUNTIF($A$2:$A$10,"="&C$1:C1)),0))

Finally, I think for the purpose of discussion we should mention UDFs. I haven't yet relied on a UDF, but if I were to I believe that using a dictionary object would be the quickest:

Private mobjDICT As Object

Public Function Distinct(ByVal rngVals As Range) As Variant()
    Dim rngCell As Range
    
    If mobjDICT Is Nothing Then
        Set mobjDICT = CreateObject("Scripting.Dictionary")
    End If
    
    For Each rngCell In rngVals
        If Not mobjDICT.Exists(Key:=rngCell.Text) Then
            mobjDICT.Add Key:=rngCell.Text, Item:=rngCell.Text
        End If
    Next rngCell
    
    Distinct = Application.Transpose(mobjDICT.Keys)
End Function

In C2:C10, confirmed with Control+Shift+Enter (note, this is an array entered formula so must be applied to the range):
=DISTINCT(A2:A10)

[Zack Barresse]

Hi all,

If I have to show people how to get unique values, it's almost always showing them how to use Advanced Filter. I'd say it's probably as much as 95% of the time. If, however, they just can't get it, I'll use VBA and make use of the Scripting Dictionary (much like Jon's last post). And if they want to use a function like that repeatedly and cause massive amounts of overhead in regards to calculation time/efficiency, well, you get what you ask for I guess. I think almost anyone can learn how to use Advanced Filter, and most of the people who say "I can't", just means, "I don't want to".

[Simon Lloyd]

Zack, i hear you!, i'm always being asked for a formula, it matters not to them that there are easier ways, they'd rather copy 'n' paste a CSE formula and copy down, and as sure as the sky is blue they always come back a number of times ".....it doesn't work", "....Gives odd results" ...etc and it's all because they cannot grasp the CSE concept or the need to commit it again should they edit the formula.

[Jon von der Heyden]

And if they want to use a function like that repeatedly and cause massive amounts of overhead in regards to calculation time/efficiency, well, you get what you ask for I guess. I think almost anyone can learn how to use Advanced Filter, and most of the people who say "I can't", just means, "I don't want to".

Ditto to this comment