Extract only unique values from a range in Excel

Posted on 2011-05-12
Last Modified: 2012-05-11
I have a range of about 7 values in  a column, but some of hte values will repeat.  I need to pull outonly unqiue values and use these in a loop.  For whatever reason, the Data Advance Filter Unique values in Excel 2003 does not work.  Is there any way to do this otherwise?  Has to be done with VBA
Question by:ssmith94015
    LVL 92

    Expert Comment

    by:Patrick Matthews
    The advanced filter should work. If it doesn't that would indicate that the values are not really distinct.

    Are there perhaps some stray characters in the entries, such as trailing spaces?

    When you tried the adv filter, did you perhaps select >1 column?

    Author Comment

    That was the first thing I checked.  In code and manully, still returns duplicates.  
    LVL 92

    Expert Comment

    by:Patrick Matthews

    It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

    Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

    Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

    Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use instead, which is not officially an EE site, but is run by people connected to EE.

    LVL 18

    Accepted Solution


    Try this function.

    Function GETUNIQUE(ByRef r As Range)
        Dim i   As Long, c As Long, d
        If TypeOf r Is Range Then
            d = r.Value2
        Else: Exit Function
        End If
        With CreateObject("scripting.dictionary")
            .comparemode = 1
            If IsArray(d) Then
                For i = 1 To UBound(d, 1)
                    For c = 1 To UBound(d, 2)
                        If Len(d(i, c)) Then
                            .Item(d(i, c)) = Empty
                        End If
                GETUNIQUE = .keys
                GETUNIQUE = d
            End If
        End With
    End Function
    'and call the function like
    Sub kTest()
    Dim a
    a = GETUNIQUE(Range("a2:a10"))
    MsgBox Join(a, vbLf)
    End Sub

    Open in new window


    Author Closing Comment

    I am so sorry I did not get back to this.  My contract is up in two days so have been frantically working on other issues.  Thank you for the help as this really was a stumbling block.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now