Free Microsoft Excel 2013 Quick Reference

List unique values of multiple columns Results

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.

Thanks in advance...

Cross-ref on the same

I am trying to use the Advanced Filter in Excel Unique Values in VBA on an already filter list, but the results I am seeing is not as expected. The problem I have is that the advanced filter for unique values is running over a list of rows that have already been filtered, and when the advanced filter is running it add values that have already been filtered out. To described the problem I have already filtered out non null dates, and another column that contains "-". However, when I apply the unique values filter on column it adds in null dates and other values from the other column.

Is there away to apply the advanced filter for unique values on an already filtered list so it does not add in rows that shouldn't be there?

Hi All,

For filtering out unique values from a column and listing them all together in an adjacent column, this formula I found on another forum:{=OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$15)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)}works just great, yet I am trying to understand exactly how its middle part works, but haven't been able to so far!
Maybe I am a bit behind on the math involved here, but could somebody please explain how the matrix transposition and multiplication works in this case, in particular - how can he compare that transposed original list is equal to part of new filtered list? and what is the second array in the MMULT(array1,array2) in this case, it seems it would be all 1/1's, or is it getting into matrix division here somehow?

I tried taking the formula apart into pieces, but after several hours still am stuck with the middle part...

I take the liberty to quote part of this 2005 post by Alan, describing the formula's use:
"...If you have a list in A1:A13 as


B1 enter either "Alan" or link to A1

In B2 enter this as an array


enter the array formula, type it in without the curly brackets, and then enter
using Shift-Ctrl-Enter and excel will put the braces on the formula.

that formula down to B2:B13.

It returns 'Not Applicable' in B6:B13 since there are no more unique entries. ..."

It seems that Alan has not posted there for a long time, so I am asking in this forum, which appears quite active and hoping with gratitude to be enlightened.



I used the advanced filter in excel to get a list of unique cell values in a column. I also need to know the values of 2 other columns in the same row. Just finding the values in two other columns on the first occurence of the filtered cell values and entering them in adjacent cells to the filtered list should do the trick most of the time. Thanks in advance.

I have a macro that I borrowed from Colo's excel help site. I understand what it is trying to do but am at a loss to apply it to my situation (not sure which variables to change I guess).

I have a bunch of data in column D2:D2152 (names of countries listed multiple times) and I want the unique values returned in Column R. Can someone help me modify this code to apply to my situation?

Thanks in advance for any help that can be offered. Always very much appreciated.

Option Explicit

Sub TestingFunction()
    Dim buf As Variant

    'Getting an unique collection from the values in Worksheet(2D array)
    buf = Array_Unique_Collection([A1:A10].Value)

    'See result
    If Not IsNull(buf) Then
        [B1].Resize(UBound(buf)).Value = Application.Transpose(buf)
    End If

    'Getting an unique collection from the 1D array
    buf = Array_Unique_Collection(Array(1, 2, 3, 1, 2, 3, 1, 2, 3))

    'See result
    If Not IsNull(buf) Then
        [C1].Resize(UBound(buf)).Value = Application.Transpose(buf)
    End If

    'If empty range as specified as an argument, this UDF returns NULL
    buf = Array_Unique_Collection([E1:E10].Value)

    'See result
    If Not IsNull(buf) Then
        [d1].Resize(UBound(buf)).Value = Application.Transpose(buf)
    End If

End Sub

Function Array_Unique_Collection(ByVal NotUniqueArry As Variant) As Variant
'returns unique collection as a 1D array
'returns NULL when there is no value
    Dim cTmp As New Collection
    Dim i As Long
    Dim aTmp As Variant
    Dim vElm As Variant

    On Error Resume Next
    For Each vElm In NotUniqueArry
        cTmp.Add CStr(vElm), CStr(vElm)
    On Error GoTo 0

    If cTmp.Count = 1 And cTmp.Item(1) = vbNullString Then
        Array_Unique_Collection = Null
        Exit Function
    End If

    ReDim aTmp(1 To cTmp.Count)
    For i = 1 To cTmp.Count
        aTmp(i) = cTmp.Item(i)
    Array_Unique_Collection = aTmp
End Function

I am trying to find a way to create a list of unique items from a named range. I have found a few solutions using filters for ranges that are contained in the one column but not named ranges that contain multiple rows and multiple columns.

Is there any way to copy all of the unique items from the named range to another location? Lets call the named range Table1 and it spans from B2 to J100.

Thanks in advance!!

I have a biggish spreadsheet of 150 columns and 14000 rows (with blanks in many cells). What I need is a summary sheet for each column showing what unique and distinct (I get the terms mixed up so sorry) values for each column. I know I could use advanced filter on each column but I don't relish this idea as this is just the first sheet of many.

I have seen the following array formula but it only works on my first column leaving me with 149 to go - it doesn't like moving to new columns and hates being on a different sheet.

{=INDEX(List (A$2:A$10),MATCH(0,COUNTIF($B$1:B1,List (A$2:A$10)),0))}

I have attached a very simple excel file to simply show what I have and what I want to get on two separate sheets.

Help pretty please.

Hi all,

I am looking for a way to list all unique entries from for e.g. row b over all sheets in my workbook.

The idea however is to have this continually updating and everytime a new unique value us added into the row it will automatically appear.

This is going to be coupled with COUNTIF to feedabck the number of times used.



Aargh! This one has kept me up up all night!

Basically, I have a folder with around 400 csv files ranging from 100k to 300meg. I want to loop through all of these files, select the data in columns E and F, filter column E for unique values and then extract E&F to a workbook at the last empty row.

I've amended some of the mighty Ron de Bruin's code to accomplish this. Unfortunately, the macro falls over itself after processing the first 20 or so sheets.

It throws the good old "The information cannot be pasted because the copy area and the paste area are not the same shape and size" at this line

It's probably something really simple, but I can't work out why it's doing this. All the csv files have the same headers and
are the same number of columns. Yet some csvs throw the error, others don't.

Full code is below. I will attempt backflips of gratitude for anyone who can help me!

Sub MergeWithFilter()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long, FNum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, DestRange As Range
    Dim rnum As Long, CalcMode As Long
    Dim rng As Range
    Dim FilterField As Integer, RangeAddress As String
    Dim ShName As Variant, RwCount As Long

    '***Change these five lines of code before you run the macro***

    ' Change this to the pathfolder location of the files.
   MyPath = "C:UsersSteeeeeveDocumentsPimmDataOBISunzipOBIS"
   'MyPath = "C:UsersSteeeeeveDocumentsPimmDataOBISunzipTest"
    ' Fill in the name of the sheet containing the data.
    ' Use ShName = "Sheet Name" to use a sheet name instead if its
    ' index. This example uses the index of the first sheet in
    ' every workbook.
    ShName = 1

    ' Fill in the filter range: A1 is the header of the first
    ' column and G is the last column in the range and will
    ' filter on all rows on the sheet.
    ' You can also use a fixed range such as A1:G2500.
    RangeAddress = Range("E1:F" & Rows.Count).Address

    ' Set the field that you want to filter in the range
    ' "1 = column A" in this example because the filter range
    ' starts in column A.
    FilterField = 1

    ' Fill in the filter value. Use the "<>" if you want to
    ' filter on the absence of a term. Or use wildcards such
    ' as "ron*" for cells that start with ron, or use
    ' "*ron*" if you look for cells where ron is a part of the
    ' cell value.
    'SearchValue = "ron"


    ' Add a slash after MyPath if needed.
    If Right(MyPath, 1) <> "" Then
        MyPath = MyPath & ""
    End If

    ' If there are no Excel files in the folder, exit.
    FilesInPath = Dir(MyPath & "*.csv")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    ' Fill the myFiles array with the list of Excel files in the
    ' folder.
    FNum = 0
    Do While FilesInPath <> ""
        FNum = FNum + 1
        ReDim Preserve MyFiles(1 To FNum)
        MyFiles(FNum) = FilesInPath
        FilesInPath = Dir()

    ' Change application properties.
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ' Add a new workbook with one sheet.
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    rnum = 1

    ' Loop through all files in the myFiles array.
    If FNum > 0 Then
        For FNum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(FNum))
            On Error GoTo 0

            If Not mybook Is Nothing Then

                On Error Resume Next
                ' Set the filter range.
                With mybook.Worksheets(ShName)
                    Set sourceRange = .Range(RangeAddress)
                End With

                If Err.Number > 0 Then
                    Set sourceRange = Nothing
                End If
                On Error GoTo 0

                If Not sourceRange Is Nothing Then
                    ' Find the last row in target worksheet.
                    rnum = RDB_Last(1, BaseWks.Cells) + 1

                    With sourceRange.Parent
                        Set rng = Nothing
                        .AutoFilterMode = False

                        ' Filter the range on the
                        ' value in filter column.
                        sourceRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
                                sourceRange.Copy BaseWks.Cells(rnum, "A")

                    End With
                End If

                ' Close the workbook without saving.
                mybook.Close savechanges:=False
            End If

            ' Open the next workbook.
        Next FNum

        ' Set the column width in the new workbook.
        MsgBox "Look at the merge results in the new workbook " & _
           "after you click on OK."
    End If

    ' Restore the application properties.
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub

Function RDB_Last(choice As Integer, rng As Range)
   Dim lrw As Long
   Dim lcol As Integer
      On Error Resume Next
      RDB_Last = rng.Find(What:="*", _
                          After:=rng.Cells(1), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
      On Error GoTo 0
End Function

I have a listbox being populated on worksheet with column counts 6 with property set to multiselect. Its working fine. What i wish to, when user selects the multiple rows in listbox, the msg box / lable caption needs to generate to count the unique values if listbox items are selected with multiple criteria of listbox.column 3, 4 & 5.
Sl. # Bno Vari Flavour Des Date
101 AD-01 CocaCola Spite 13/04/2012
102 AB02 Pepsi Mango 13/04/2012
103 AD-02 ThumpUp Lime 13/04/2012
104 AB03 Pepsi Orange 13/04/2012
105 AD-03 Pepsi Mango 13/04/2012
106 AB04 CocaCola Spite 13/04/2012
107 AD-04 ThumpUp Lime 13/04/2012
108 AB05 ThumpUp Lime 13/04/2012
109 AD-05 CocaCola Spite 13/04/2012
and so on………………….
The result should be
Vari Flavour Counts
CocaCola Spite 3
ThumpUp Lime 3
Pepsi Orange 1
Pepsi Mango 2
And should send a notification thru mail of the above summery..
Any help………….
This is a ongoing process, once the mail send the new list will be added and the same process will be done by user.

I have a workbook with 10 pages. On each page there are entries in columns A and B. I used the same headings on each column on each page of "Code" and "Description" and I have given the range on each page a different name. Although the code numbers may repeat on both the same page or on different pages, each instance of a code has the unique description associated only with that code.

Thus, each page has 500 rows that look like this:

Code Description

AEF26 Operations
AGW56 Payroll
JRT24 Production
AGW56 Payroll

I wanted to get a single list of unique codes and their matching descriptions and I want to do this by using a multiple range pivot table, not by using a macro or by sorting.

When I run the pivot I end up with a unique list of codes, just as I wanted, but instead of seeing the second (description) column values, all I see is the name "description" and there appears to be no way to go into the pivot toolbar to change things so that I get a true list of unique values for both columns A and B. Any suggestions as to why the multiple range pivot table does not work the same way that a single range pivot table works?

Finding unique values using 2 conditions and UIDs with multiples or subsequent values -- combination of INDEX MATCH FREQ ROW INDIRECT

I have a set of data in a sheet: 'Matrix'.

Column F contains part names (contains multiples of each type--named the range matrix_pn).

Column A contains space vehicle numbers (Values = 1-12; Rows 2 - ~1000; range named matrix_sv).

Column H contains serial numbers (numbers or text; rows 2 - ~1000; named range matrix_sn).

There is a grid on another sheet: 'Summary Matrix'.

The grid is as such: Part number vertically in Column E (static data, matching names in column F of the Matrix sheet); horizontally, Columns G through R in Row 2 are the Space Vehicle Numbers 1-12.

Sample Data: 
-- Matrix Sheet -- 
|| Col A (SV #) || Col F (P/N) || Col H (S/N) || 
-- Summary Matrix Sheet -- 
|| Col E (P/N) || Col G (SV1) || (SV2) || ... || Col R (SV12) || 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The solution for the grid is an array formula (that I cannot piece successfully on my own) that uses the part name (Col E) in the 'Summary Matrix' sheet to find unique serial numbers in the 'Matrix' sheet using 2 conditions: the Space Vehicle and the Part Name. Another complication is that the part name list in the Summary Matrix may have duplicates as well--when for example 2 parts of that type are on the vehicle.

I started to use this formula in Cell G4--the first cell of the grid:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This produces the serial numbers correctly unless there is a duplicate part number, where it repeats the first serial number found.

Please, your expedient assistance would be extremely appreciated.


Sample Workbook:

I have done a great deal of searching and have found parts to my puzzle but not the whole picture. I am looking to combine (not concatenate) multiple columns in different worksheets. From this list, I would like to generate a list of unique values. I have pieced other solutions to parts of my problem yet I end up with a unique list that ends up failing in subsequent vlookup formulas, for example.

I have attached a file to be more specific; I would like to take all of the values from all of the 'column A's in the various 'Exhibits' worksheets and populate a unique list of these values on my Master worksheet starting with A5. I would also like this to be as automatic as possible...the idea is that this is a template where the 'Exhibits' will be changed (new numbers in the 'column A's, sometimes 1 exhibit, sometimes 2, etc (no more than 10 or so)).

I am very grateful for any quidance!

I have values in Worksheet 1, spread over A1:D25 and A200: D250.
In worksheet2 I have values again from A1:D25 and A200:D250.

Is is possible to only get the unique values of those 4 ranges with the advanced filter? They all need to be shown in eg worksheet 3 starting in A1, (so kinda merged in a sense)?

Is that a thing more for a UDF, or is there a excel function/option that does exactly that?

I have been looking for ages for that kind of function/option, since I thought it must be possible. But this sure does not look to be a standard functionality, or is it?
Is there a (free) add-in that might do this kind of thing?

I found this code on some office help page:

     ' Place column header for temporary sort area.
    Range("IV1").Value = "Numbers" 
     ' Move numbers to temporary sort location.
    For Each cell In Selection 
        Range("iv65536").End(xlUp).Offset(1, 0) = cell.Value 
    Next cell 
     ' Sort numbers in ascending order.
    Range("IV1", Range("IV1").End(xlDown)).Sort Key1:=Range("IV2"), _ 
    Order1:=xlAscending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
     ' Move sorted data back to original sheet location.
    Selection(1, 1).Activate ' Make sure the ActiveCell is the
     ' top left of Selection first.
    CCnt = Selection.Columns.Count 
    RCnt = Selection.Rows.Count 
    CellCnt = Selection.Cells.Count 
    Tcell = 2 
    For c = 1 To CCnt 
        For r = 1 To RCnt 
            Range(ActiveCell.Address).Offset(r - 1, c - 1).Value = _ 
            Range("iv" & Tcell).Value 
            Tcell = Tcell + 1 
        Next r 
    Next c 
     ' Clean up temporary sort location.
    Range("IV1", Range("IV1").End(xlDown)).Clear 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It kinda does what I needed, but it lists the actual data in the same spot it used to be. I want to be able to list the sorting in a different column on a different sheet and in 1 column only. Is this difficult to modify so it becomes a UDF or is this something totally different?


I have a dynamic list of names in an array (e.g. contained in multiple columns and rows) that I need to concatenate into a single cell. I only want unique values, and they must be separated by commas. I'm open to VBA, but would prefer formulas if possible (even if it requires downloading an add-in*).

Extra information:
I've seen questions similar to mine, most of which use morefunc's uniquevalues() or mconcat(uniquevalues()). However--so far as I know--these make no provision for delimiters. I've also seen user-defined functions in vba, but haven't been able to make any of these work so far.


*I read this article by Stephanie Viot (posted by Dave Hawley), which referenced a "Copy Unique Values" add-in. The article claimed to have the add-in available for download at the end, but I couldn't find it in the article (or anywhere else on the web, despite the large number of sites that posted the same article). Perhaps I'm looking in the wrong place, but sending me a link to that download may be an easy solution to this.


Thanks in advance.


I have a list of project names (some of which appear multiple times) in a column.

Is it possible to populate my combobox with only unique vaklues in my columns?


I have a column that sometimes has multiple values in a single cell. I want to pull the unique values from that cell using a formula.

Column A

2) Dog

In cell B2 I want Dog, Cell B3: Cat and B4: Bird,Cat,Dog, B5:Frog,Dog

I want a formula I can type in B and have it look to the Cell and extract unique values

I have a large dataset of patients' vitamin B12 levels over time. Each row (~30K) has a single lab value. When a single patient has multiple different levels, they are each listed in a separate row with the unique patient identifier in Column A, the lab value in column B and the date the test was done in column C. The number of rows vary for each patient (based on the # of tests they have). I need to re-arrange all the data for any given patient into a single row with the first lab value in column D, the date the first test was done in column E, the 2nd lab value in column F, the date the second test was done in column G, the 3rd lab value in H, etc.

Is there a way to do this?

How can I sum the unique values (project ID's) in a list (project time log?)

A sheet in the workbook contains the Project Time Log with the columns:
Project ID, Date, Work Days (ex: 0.25 would equal 1/4 of a day.) A single
entry would be something like: SalesReportAugust, 9/22/06, 0.5. The
'SalesReportAugust' project would be listed multiple times with the dates it
was worked on and how much time it required, in fraction of a day.

What I need:
On another sheet (Project Workload Summary) I would like the unique Project
ID's to list with the total of days worked on. The tricky part is the list
of unique Project ID's. I know it can be done with a query or pivot table or
probably VBA but I would like a solution that does not require one to perform
any 'action' to update the list of unique Project ID's. Ideally, I would
prefer that every time another new Project ID is entered on the Project Time
Log sheet, it would automatically appear on the Project Workload Summary
sheet with its total thus far.

Is there a fancy formula that can accomplish this, maybe with a dynamic
range name mixed in?

I am not sure if the following is possible, but if anyone knows, I
would greatly appreciate some help here.
What I have is the below columns of data (in A through C) (data of
course has been changed)

12345 zt12345a PizzaHutLarge
12345 xr12345a PizzaHutSmall
56789 ab56789b DominosLarge
56789 df56789a DominosSmall
23567 ty23567h PapaJohnLarge
23567 rp23567q PapaJohnSmall
23567 qw23567a PapaJohnMedium

These columns go down a couple thousand rows of data. What I would
like to allow the user to do is copy and paste unique values found in
column A into another cell column (in D let's say) and then in column
E it would list all the values from B associated with it.

For example, if the user pasted 12345 & 23567 in column D, then in
column E, the result would be


Can this be done? Any help is greatly appreciated.

Thanks in advance.


ps - I have a workaround for this situation, but I wanted to see if
the above was possible.