Announcement

Collapse
No announcement yet.

List Unique Values Of Multiple Columns

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • List Unique Values Of Multiple Columns

    I need a UDF to make live easier. I have a huge excel file and I need a final function (I suppose a UDF) that lists all values from a range spanning over multiple columns and rows.
    I would not really like a macro, since it does not update when certain values are changed.

    It's part of a bigger functionality, but I need this one, really bad, since it spans over 6000 rows as the search range, and 7000 as the search values. In short:
    I got to list all the unique values. They are in specific cells on worksheet 1 (A1:C5 and A25:C31) and should be listed on worksheet2 starting in field A2. As 'simple' as that. Nothing more, nothing less, just list all unique values of a certain range, which I specify. If it can not be done, by selecting the two fields separately, it's no problem at all to list all values in eg A1:C6000. Do note that some of the cells will be blank, and I don't need blanks. I only need the actual data sets.

    I added a sample excel file, that shows a representation of the data. I can not upload the actual file, since it's way too big (60 MB). So a sample file should give you the idea.
    Three sheets:
    Data 1: some value lists multiple columns
    Data 2: some value lists, again multiple columns
    Desired result: the result as it should be listed.
    example.xls.zip


    Thanks in advance...

    Cross-ref on the same http://www.ozgrid.com/forum/showthread.php?t=86444

  • #2
    Re: List Unique Values Of Multiple Columns

    This UDF might help. Its entered as an array formula.

    Select the whole range of cells where you want the results. Then type

    =Uniques(A1:C6000) in the formula window and press Ctrl-Shift-Enter (Cmd-Return for Mac)

    The result range must be either all in one row or all in one column. If there are more result cells than unique values, those cells will show "".

    Code:
    Function Uniques(ByVal inputRange As Range)
        Dim inputArray As Variant
        Dim myColl As New Collection
        Dim xVal As Variant
        Dim outArray() As String
        
        On Error Resume Next
        With inputRange
            inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
        End With
        On Error GoTo 0
        
        On Error Resume Next
        For Each xVal In inputArray
            myColl.Add Item:=CStr(xVal), key:=CStr(xVal)
        Next xVal
        On Error GoTo 0
        myColl.Remove vbNullString
        
        ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
        For xVal = 1 To myColl.Count
            outArray(xVal) = myColl(xVal)
        Next xVal
        
        If Application.Caller.Columns.Count = 1 Then
            Uniques = Application.Transpose(outArray)
        Else
            Uniques = outArray
        End If
    
    End Function
    This function will error if called from a VB routine, it can only be called from a spreadsheet formula.

    Comment


    • #3
      Re: List Unique Values Of Multiple Columns

      This is very very cool. It works just perfect. Many, really many thanks.

      Should learn how to code VBA, quite powerful.

      Comment


      • #4
        Re: List Unique Values Of Multiple Columns

        This is a sturdier version that preserves data type. The above returns only text values, this returns the data types found in the range (including errors).
        Code:
        Function Uniques(ByVal inputRange As Range)
            Dim inputArray As Variant
            Dim myColl As New Collection
            Dim xVal As Variant
            Dim outArray() As Variant
            
            On Error Resume Next
            With inputRange
                inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
            End With
            On Error GoTo 0
            
            On Error Resume Next
            For Each xVal In inputArray
                myColl.Add Item:=xVal, key:=(CStr(xVal) & TypeName(xVal))
            Next xVal
            myColl.Remove "String"
            On Error GoTo 0
            
            
            ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
            For xVal = 1 To UBound(outArray)
                outArray(xVal) = vbNullString
            Next xVal
            
            For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
                outArray(xVal) = myColl(xVal)
            Next xVal
            
            If Application.Caller.Columns.Count = 1 Then
                Uniques = Application.Transpose(outArray)
            Else
                Uniques = outArray
            End If
        
        End Function

        Comment


        • #5
          Re: List Unique Values Of Multiple Columns

          Speechless, thank you very much.

          Comment


          • #6
            Re: List Unique Values Of Multiple Columns

            Hi,

            this is excellent, much easier to use than trying to bolt together existing excel formulae.

            I was just wondering though, how would you get it to output results into a single column? So you feed it say 3 columns worth of entries, and it spits out just one column of unique values from all 3 columns.

            Originally posted by mikerickson View Post
            This is a sturdier version that preserves data type. The above returns only text values, this returns the data types found in the range (including errors).
            Code:
            Function Uniques(ByVal inputRange As Range)
                Dim inputArray As Variant
                Dim myColl As New Collection
                Dim xVal As Variant
                Dim outArray() As Variant
                
                On Error Resume Next
                With inputRange
                    inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
                End With
                On Error GoTo 0
                
                On Error Resume Next
                For Each xVal In inputArray
                    myColl.Add Item:=xVal, key:=(CStr(xVal) & TypeName(xVal))
                Next xVal
                myColl.Remove "String"
                On Error GoTo 0
                
                
                ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
                For xVal = 1 To UBound(outArray)
                    outArray(xVal) = vbNullString
                Next xVal
                
                For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
                    outArray(xVal) = myColl(xVal)
                Next xVal
                
                If Application.Caller.Columns.Count = 1 Then
                    Uniques = Application.Transpose(outArray)
                Else
                    Uniques = outArray
                End If
            
            End Function

            Comment


            • #7
              Re: List Unique Values Of Multiple Columns

              Actually, I just answered my own question. Didn't read your instruction clearly enough the first time. Sorry about that...

              In case anyone else makes the same mistake, I simply entered the formula into a cell, ctrl+shift+entered it, then drag copied it down to where I wanted. This is not correct, you must first select the range in which you want the answers to appear (i.e. a single column range), type the formula then press ctrl+shift+enter.

              It works superbly, thanks.

              Comment


              • #8
                Re: List Unique Values Of Multiple Columns

                Hello and thank you for this. It's almost exactly what I am looking for, except I have a column in 3 workbooks that I want to combine into one list of unique values in a 4th workbook. When I try to use this to select multiple columns of data I get an #VALUE error. Is there a way to do this?

                Comment


                • #9
                  Re: List Unique Values Of Multiple Columns

                  Hi,

                  I used this code and it worked well when the output array was 50,000 cells or less. It stopped working at 100,000 cells. I imagine there's some sort of cutoff in between those numbers. On the other hand the input array had no trouble handling 200,000 or more cells (as long as the output array was 50,000 or less).

                  In the end, I'd want it to accept about 800,000 cells as inputs and output to approximately 150,000 to 200,000 cells. Is this possible? Do I need to alter something? Is there an implicit maximum on the number of cells the output array can handle?

                  Thanks!

                  Originally posted by mikerickson View Post
                  This is a sturdier version that preserves data type. The above returns only text values, this returns the data types found in the range (including errors).
                  Code:
                  Function Uniques(ByVal inputRange As Range)
                      Dim inputArray As Variant
                      Dim myColl As New Collection
                      Dim xVal As Variant
                      Dim outArray() As Variant
                      
                      On Error Resume Next
                      With inputRange
                          inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
                      End With
                      On Error GoTo 0
                      
                      On Error Resume Next
                      For Each xVal In inputArray
                          myColl.Add Item:=xVal, key:=(CStr(xVal) & TypeName(xVal))
                      Next xVal
                      myColl.Remove "String"
                      On Error GoTo 0
                      
                      
                      ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
                      For xVal = 1 To UBound(outArray)
                          outArray(xVal) = vbNullString
                      Next xVal
                      
                      For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
                          outArray(xVal) = myColl(xVal)
                      Next xVal
                      
                      If Application.Caller.Columns.Count = 1 Then
                          Uniques = Application.Transpose(outArray)
                      Else
                          Uniques = outArray
                      End If
                  
                  End Function

                  Comment


                  • #10
                    Re: List Unique Values Of Multiple Columns

                    Originally posted by mikerickson View Post
                    This UDF might help. Its entered as an array formula.

                    Select the whole range of cells where you want the results. Then type

                    =Uniques(A1:C6000) in the formula window and press Ctrl-Shift-Enter (Cmd-Return for Mac)

                    The result range must be either all in one row or all in one column. If there are more result cells than unique values, those cells will show "".

                    Code:
                    Function Uniques(ByVal inputRange As Range)
                        Dim inputArray As Variant
                        Dim myColl As New Collection
                        Dim xVal As Variant
                        Dim outArray() As String
                        
                        On Error Resume Next
                        With inputRange
                            inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
                        End With
                        On Error GoTo 0
                        
                        On Error Resume Next
                        For Each xVal In inputArray
                            myColl.Add Item:=CStr(xVal), key:=CStr(xVal)
                        Next xVal
                        On Error GoTo 0
                        myColl.Remove vbNullString
                        
                        ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
                        For xVal = 1 To myColl.Count
                            outArray(xVal) = myColl(xVal)
                        Next xVal
                        
                        If Application.Caller.Columns.Count = 1 Then
                            Uniques = Application.Transpose(outArray)
                        Else
                            Uniques = outArray
                        End If
                    
                    End Function
                    This function will error if called from a VB routine, it can only be called from a spreadsheet formula.
                    I tried this out with the example Excel file and it worked just as expected. I am not having the same luck the file I want to apply it to. Should this VB code be transferrable to any spreadsheet where I can define the array in the =Uniques function or is the code written in a way to make it compatible with just the example spreadsheet? Please help! This functionality will be useful in many existing spreadsheets I am using!

                    Comment


                    • #11
                      Re: List Unique Values Of Multiple Columns

                      You could have 'helper' columns in the sheet where you are using the UDF. Using 3 columns in the sheet, have the values of the three subject columns feed through to them (using a simple '=Sheet1!A1' type formula in each of the cells), and then use the UDF on those three columns. Should do what you want.

                      Comment


                      • #12
                        Re: List Unique Values Of Multiple Columns

                        The UDF accepts a range as its argument. The workbook which holds that range does not matter. (It should be an open workbook).

                        When used in a worksheet formula, the code for the UDF has to be in the same workbook as the cells holding the formula.

                        Comment


                        • #13
                          Re: List Unique Values Of Multiple Columns

                          Originally posted by mikerickson View Post
                          This UDF might help. Its entered as an array formula.

                          Select the whole range of cells where you want the results. Then type

                          =Uniques(A1:C6000) in the formula window and press Ctrl-Shift-Enter (Cmd-Return for Mac)

                          The result range must be either all in one row or all in one column. If there are more result cells than unique values, those cells will show "".

                          This function will error if called from a VB routine, it can only be called from a spreadsheet formula.
                          I've tried using this to extract a list of unique values from a range of cells (currently 7 columns x 35 rows... but will have new rows added on an ongoing basis), but I get a #Value! error.

                          Are there any alterations that need to be made to work in Excel 2010?

                          Thanks!

                          Comment


                          • #14
                            Re: List Unique Values Of Multiple Columns

                            damienh7,

                            Welcome to OZgrid.

                            Please take time to read the forum rules.

                            Do not post your question in threads started by others - - this is known as thread hijacking.
                            Always start a new thread for *YOUR* question. If you find it helpful to clarify your needs you can include a link to this ( or any other ) thread.

                            When starting a new thread be sure to give it a search-friendly title that aptly describes your need.
                            Start a new thread for your question
                            AAE
                            ----------------------------------------------------

                            Forum Rules | Message to Cross Posters | How to use Tags

                            Comment


                            • #15
                              Re: List Unique Values Of Multiple Columns

                              The code for the UDF should be in the same workbook as the cells holding the formula.

                              Comment

                              Working...
                              X