VBA: How do I get unique values in a column and insert it into an array?
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 = David Array = Johnathan Array = Peter Array = Louis
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.
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.
- 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
collectionis a poor choice compared to a
Existsfunction, 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
Value2help 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
Existshow did you then add the item? Using
Item? In my test of 100k names, they were exactly equal, although I was only using
- 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
Itemapproach to my
Existsapproach (I guess .
Existsbehind 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
Existsapproaches 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.