VBA: How do I get unique values in a column and insert it into an array?

quickest way to get all unique values of a column in vba?
excel vba extract unique values from multiple columns
vba array unique values
vba remove duplicates from array
excel copy unique values to another worksheet vba
excel vba count unique values in column
excel extract unique values from multiple columns
vba unique values in table

I have seen multiple codes regarding this topic but I can't seem to understand it.

For instance, if I have a column that records people names, I want to record all unique names into the array.

So if I have a column of names

David
Johnathan
Peter
Peter
Peter
Louis
David

I want to utilize VBA to extract unique names out of the column and place it into an array so when I call the array it would return these results

Array[0] = David
Array[1] = Johnathan
Array[2] = Peter
Array[3] = Louis

Despite a Collection being mentioned and being a possible solution, it is far more efficient to use a Dictionary as it has an Exists method. Then it's just a matter of adding the names to the dictionary if they don't already exist, and then extracting the keys to an array when you're done.

Note that I've made the name comparisons case-sensitive, but you can change that if necessary, to case-insensitive.

Option Explicit

Sub test()

   'Extract all of the names into an array
    Dim values As Variant
    values = Sheet1.Range("Names").Value2 'Value2 is faster than Value

    'Add a reference to Microsoft Scripting Runtime
    Dim dic As Scripting.Dictionary
    Set dic = New Scripting.Dictionary

    'Set the comparison mode to case-sensitive
    dic.CompareMode = BinaryCompare

    Dim valCounter As Long
    For valCounter = LBound(values) To UBound(values)
        'Check if the name is already in the dictionary
        If Not dic.Exists(values(valCounter, 1)) Then
            'Add the new name as a key, along with a dummy value of 0
            dic.Add values(valCounter, 1), 0
        End If
    Next valCounter

    'Extract the dictionary's keys as a 1D array
    Dim result As Variant
    result = dic.Keys

End Sub

VBA code to get unique entries in a column into an array, This example filters values in Sheet1 column A (A1 contains the column heading) and copies the unique values to Sheet2 column A, from where  Ive traced this code, it goes through an entire column, if the entry is a new one it adds it to an array, then goes to next column position takes it compares to previous, if its same, goes to next one until its gone down the entire column collecting all the unique values.

use Dictionary object and build a Function that returns your array

Function GetUniqeNames(myRng As Range) As Variant
    Dim cell As Range

    With CreateObject("Scripting.Dictionary") ' instantiate and reference a Dictionary object
        For Each cell In myRng ' loop through passed range
            .Item(cell.Value2) = 1 ' store current cell name into referenced dictionary keys (duplicates will be overwritten)
        Next
    GetUniqeNames = .keys ' write referenced dictionary keys into an array
    End With
End Function

that you can exploit in your main code as follows

Sub main()
    Dim myArray As Variant

    With Worksheets("mysheet") ' change "mysheet" to your actual sheet name
        myArray = GetUniqeNames(.Range("A1", .Cells(.Rows.Count, 1).End(xlUp))) ' this will take the referenced sheet column A range from row 1 down to last not empty one
    End With

End Sub

[SOLVED] Storing unique values in array, I am processing an Excel spreadsheet, attempting to store the unique values from one of the columns (location) to an array. If the location  In order to use .Value to get a value from a cell, we need to refer to a single cell from the range of cells in our variable. The way we do that is with the Cells() VBA function. The Cells() function is a way to take a range of cells and return a single cell from the set.

Is this a VBA question or a question about programming logic? Use a loop on the column with the data. Check each name against the list of existing data items. If it exists in the list, move on the the next name. If it does not exist in the list, add it.

The "list" is a concept, not a concrete tool. It can be a VBA dictionary, if you are comfortable using that. Or it can be a VBA array, which may not perform as fast as a dictionary, but may be more familiar.

Then again, if you add the data to the Excel Data Model, you can use the Distinct aggregation of a pivot table to list out the unique values.

Without more background it's hard to tell if VBA or Data Model is your best approach. Many VBA solutions get created because people are not aware of Excel's capabilities.

How to Extract Unique Values from ANYTHING in Excel with VBA , If we want to get a single-column list of the uniques across both columns there, the code below will do the trick. Public Sub WriteUniquesToNewSheet(). What is the VBA Array and Why do You Need It? A VBA array is a type of variable. It is used to store lists of data of the same type. An example would be storing a list of countries or a list of weekly totals. In VBA a normal variable can store only one value at a time.

You could use Excel functionality like that.

Sub UniqueNames()

Dim vDat As Variant
Dim rg As Range
Dim i As Long

    Set rg = Range("A1:A7")

    rg.RemoveDuplicates Columns:=Array(1), Header:=xlNo
    With ActiveSheet
        vDat = WorksheetFunction.Transpose(.Range("A1:" & .Range("A1").End(xlDown).Address))
    End With

    For i = LBound(vDat) To UBound(vDat)
        Debug.Print vDat(i)
    Next i

End Sub

Code is based on your example data, i.e. I put your data into column 1. But the code will also alter the table. If you do not want that you have to use other solutions or put the data beforehand in a temporary sheet.

3 Ways to extract unique values from a range in Excel, To make it more clear, unique values are the values that appear in a column only Advanced Filter; Index- Match Array Formula; Excel Macro (VBA); Remove  Tweak one of the formulas according to your dataset. Enter the formula in the first cell of the unique list (B2 in this example). If you are using the array formula, press Ctrl + Shift + Enter. If you've opted for the regular formula, press the Enter key as usual.

Retrieving unique values from a range or array, (and simpler) usage to return unique values from a worksheet range or VBA array. 'Convert range to array and count rows and columns. Unique values would be a distinct list. To make it more clear, unique values are the values that appear in a column only once. Sample File Click on the link below and download the excel file for reference. We will use this workbook to demonstrate methods to find unique values from a column. Extract unique values from a column

How to extract unique values from multiple columns in Excel?, How do I extract unique values from multiple columns in Excel? Hi, I want to extract all the unique values from column A starting with A2 to the last cell of column A that has a value and copy those values into cell B2 all the way down to whatever the last cell of column B is. I have row titles in cells A1 and B1. Example: Data Before Macro

Add unique values using VBA in Microsoft Excel, Add another column to find the sum of values in each row. ArrowTotal Once we have the unique numbers in the collection, we can add them in array. Normally in excel worksheet we have two different methods to add or insert columns one being the keyboard shortcut and another by using the right-click and insert method but in VBA we have to use insert command and entire column statement to add any column together, the trick to this is that if we need to insert a single column we give a single column reference but for the multiple columns we give multiple column references.

Comments
  • An example of source data, results required and what you have tried would be a good start...
  • You can use collection object. stackoverflow.com/a/8305733/5829910
  • @Zsmaster A collection is a poor choice compared to a Dictionary. A Dictionary has an Exists function, which makes checking for existing names much easier, and more importantly, much faster.
  • Watch: Excel VBA Introduction Part 25 - Arrays and Excel VBA Introduction Part 39 - Dictionaries
  • Why is value2 faster please?
  • @QHarr Probably because it doesn't check the cell format
  • @RonRosenfeld haha - I was just about to post the same link - it's a great explanation. There's also the Value2 help page. The only difference between this property and the Value property is that the Value2 property doesn't use the Currency and Date data types.
  • @TinMan, when using Exists how did you then add the item? Using Add or Item? In my test of 100k names, they were exactly equal, although I was only using Timer() resolution.
  • Thanks to you all.
  • Reading one cell at a time is very slow - better to read all of the values into an array. Also, better to check the key doesn't exist than to update the value every time. And finally, you haven't been clear about case sensitivity.
  • OP's didn't say anything about data amount, but using an array will certainly speed things up. While checking for existing keys would be slow, too: never made a comparison but I'm always satisfied with overwriting solution. Finally, as to case sensitivity, OP's data were clean and clear
  • I assumed the OP wasn't going to include the entire list, and thought it better to provide a solution that scales well. I did a quick check on 100k names, albeit repeats of the 7 names in the OP. There's no discernible difference in your Item approach to my Exists approach (I guess .Item must check Exists behind the scenes - good to know). But the array definitely speeds things up. Results: 100k names using For each cell in rng = 0.211 seconds, 100k names using an array from the range = 0.039 seconds. About 5.4 times faster.
  • thanks for the feedback. since Item and Exists approaches perform the same, the former leads to less code, which I prefer. as for the array approach, it's definitively faster as expected, though 0.2 secs for 100k names should be affordable...
  • Checking each cell, one at a time is slow, particularly for large lists. Also, checking if an item exists in an array is a very expensive operation compared to checking if an item exists in a dictionary. See my answer for an example.
  • Replace "array" with "dictionary" and the concept is the same. I was trying to provide programming logic that is independent of a particular tool. I'll adjust my answer to reflect that.