Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: List Unique Values Of Multiple Columns

  1. #1
    Join Date
    1st March 2008
    Posts
    18

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd April 2007
    Posts
    3,490

    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 "".

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    1st March 2008
    Posts
    18

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd April 2007
    Posts
    3,490

    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).
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    1st March 2008
    Posts
    18

    Re: List Unique Values Of Multiple Columns

    Speechless, thank you very much.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    16th March 2011
    Posts
    4

    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.

    Quote 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).
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    16th March 2011
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    7th June 2012
    Posts
    1

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    7th June 2012
    Posts
    1

    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!

    Quote 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).
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    7th June 2012
    Posts
    1

    Re: List Unique Values Of Multiple Columns

    Quote 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 "".

    VB:
    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!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Unique values between two columns
    By B2bFall2005 in forum EXCEL HELP
    Replies: 4
    Last Post: July 27th, 2006, 04:06
  2. Replies: 10
    Last Post: November 28th, 2005, 07:44
  3. Replies: 6
    Last Post: December 4th, 2004, 03:15

Bookmarks

Posting Permissions

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