Unique List From Array

JamesL

Board Regular
Joined
Apr 21, 2004
Messages
113
Hi,

Is it possible to get a unique list of strings from an array without writing them to a worksheet first?

Example:

Names added to NameArray(6,0) = James, Bob, Jane, James, Nick, Bob

Run a procedue to go through NameArray and output a unique set of names to a worksheet. e.g James, Bob, Jane, Nick

Thanks in advance,
James
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, James,

two variations on the same theme
Code:
Option Explicit

Sub test1()
Dim arr As Variant
Dim dic As Object
Dim i As Integer

Set dic = CreateObject("Scripting.Dictionary")
arr = Array("James", "Bob", "Jane", "James", "Nick", "Bob")
    For i = 1 To UBound(arr)
    If Not dic.exists(arr(i)) Then dic.Add arr(i), Nothing
    Next

arr = dic.keys
Range("A1").Resize(UBound(arr) + 1, 1) = Application.Transpose(arr)


End Sub

Sub tes2()
Dim arr As Variant
Dim dic As Object
Dim i As Integer

Set dic = CreateObject("Scripting.Dictionary")
arr = Array("James", "Bob", "Jane", "James", "Nick", "Bob")
On Error Resume Next
    For i = 1 To UBound(arr)
    dic.Add arr(i), Nothing
    Next

arr = dic.keys
Range("A1").Resize(UBound(arr) + 1, 1) = Application.Transpose(arr)


End Sub
kind regards,
Erik
 

JamesL

Board Regular
Joined
Apr 21, 2004
Messages
113
Hi,

This code works fine. However i have added the names so that the code reads:

For RwS = 1 to 10
Array(rw, 0) sheet1.cells(RwS, 1)
rw=rw+1
Next RwS

where RwS was the number of rows containing strings. I try the following from the code below (in bold) and it debugs:

arr = Array(rw, 0)

How do make arr add all the strings for the code below to work?

Thanks in advance,
James


Code:
Option Explicit

Sub test1()
Dim arr As Variant
Dim dic As Object
Dim i As Integer

Set dic = CreateObject("Scripting.Dictionary")
arr = Array("James", "Bob", "Jane", "James", "Nick", "Bob")
For i = 1 To UBound(arr)
If Not dic.exists(arr(i)) Then dic.Add arr(i), Nothing
Next

arr = dic.keys
Range("A1").Resize(UBound(arr) + 1, 1) = Application.Transpose(arr)


End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
try this
Code:
Option Explicit

Sub test1()
Dim arr As Variant
Dim dic As Object
Dim i As Integer

Set dic = CreateObject("Scripting.Dictionary")
arr = sheet1.Range("A1:A10")
    For i = 1 To UBound(arr)
    If Not dic.exists(arr(i, 1)) Then dic.Add arr(i, 1), Nothing
    Next

arr = dic.keys
Range("A1").Resize(UBound(arr) + 1, 1) = Application.Transpose(arr)


End Sub
 

Forum statistics

Threads
1,140,941
Messages
5,703,299
Members
421,289
Latest member
java

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top