Solved

Excel VBA 2010 - how to create a single dimension with unique values array from table column

Posted on 2014-02-10
3
2,210 Views
Last Modified: 2014-02-11
I have a workbook that has a defined table on a sheet.  I want to get the unique values from one of the columns in the table and put those unique values in an array so I can loop over them.  A blank value should count as a unique value.   How can this be accomplished? My table name is T_201311 and the column name is EnterpriseID.
0
Comment
Question by:mamuscia
  • 2
3 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39848915
The easiest way to get a list of unique values is using Advanced Filter on that column and writing the unique results to a worksheet range. Here is a code snippet that uses that method to put the unique values in an empty column to the right of the used range. As written, it hides that column and creates a named range that points to the list of unique items (excluding header).
    Set rg = .UsedRange
    Set rg = rg.Cells(1, 1).Offset(0, rg.Columns.Count + 1)
    .Range("A6").CurrentRegion.Columns(1).AdvancedFilter xlFilterCopy, , CopyToRange:=rg, Unique:=True
    Set rg = rg.CurrentRegion
    Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1)
    rg.EntireColumn.Hidden = True

Open in new window


Another way to do it is to create a dictionary object, which doesn't tolerate duplicates.
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39848956
Here is a sub & function using the Scripting Dictionary collection to make a list of the unique values in your table. The function returns the unique items in an array.

Note that I set a reference to "Microsoft Scripting Runtime" by checking its box in the Tools...Reference menu item.
Function Uniques(sTableName As String, sColumnName As String) As Variant
Dim dic As Scripting.Dictionary
Dim i As Long, n As Long
Dim v As Variant, vData As Variant, vResults As Variant
Dim tbl As ListObject
Dim col As ListColumn
Set dic = CreateObject("Scripting.Dictionary")
With ActiveSheet
    Set tbl = .ListObjects(sTableName)
    Set col = tbl.ListColumns(sColumnName)
    vData = col.DataBodyRange.Value
End With
On Error Resume Next
For Each v In vData
    dic.Add v, v
Next
On Error GoTo 0
    n = dic.Count
ReDim vResults(1 To n)
For i = 1 To n
    vResults(i) = dic.Items(i - 1)
Next
Uniques = vResults
End Function

Sub Test()
Dim v As Variant
v = Uniques("T_201311", "EnterpriseID")
Range("Q1").Resize(UBound(v)).Value = Application.Transpose(v)
End Sub

Open in new window

0
 

Author Closing Comment

by:mamuscia
ID: 39850071
thanks. this works and gives me the array I wanted.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now