Results 1 to 9 of 9

Thread: Displaying Unique values from a list

  1. #1
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0

    Displaying Unique values from a list



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0
    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 )

  3. #3
    Acolyte Jon von der Heyden's Avatar
    Join Date
    Mar 2011
    Location
    Stellenbosch,South Africa
    Posts
    24
    Articles
    0
    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.
    Last edited by Jon von der Heyden; 2011-04-19 at 01:51 PM.
    Regards,
    Jon von der Heyden

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    I know this isn't a formula, but most of my users are happy just looking at the Autofilter list on a table.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    Quote Originally Posted by Ken Puls View Post
    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
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  6. #6
    Acolyte Jon von der Heyden's Avatar
    Join Date
    Mar 2011
    Location
    Stellenbosch,South Africa
    Posts
    24
    Articles
    0
    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:

    Code:
    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)
    Last edited by Jon von der Heyden; 2011-04-20 at 01:57 PM.
    Regards,
    Jon von der Heyden

  7. #7
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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".
    Regards,
    Zack Barresse

  8. #8
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    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.
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  9. #9
    Acolyte Jon von der Heyden's Avatar
    Join Date
    Mar 2011
    Location
    Stellenbosch,South Africa
    Posts
    24
    Articles
    0
    Quote Originally Posted by Zack Barresse View 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".
    Ditto to this comment
    Regards,
    Jon von der Heyden

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •