Once worksheet data is in array, how to eliminate duplicates

jmichaelp

Board Regular
Joined
Aug 18, 2003
Messages
98
Hi, I'm reading multi-column data into an array using the "array = range.value" method. Once I have the data in the array, I need to remove any duplicates. It would be easiest I suppose to use advanced filter with the unique option on the data before I read it in, but for various reasons that won't work in this case. I could put the data into a collection first using examples others have posted, but how do I get the non-duped collection back into the array I'm working with? Thanks for your help!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

How about something like
Code:
Sub aaa()
 arr = Range("a1:c3")
 Dim nodupes As New Collection

 On Error Resume Next
 For Each ce In arr
  nodupes.Add Item:=ce, key:=CStr(ce)
 Next ce
 On Error GoTo 0
 Set arr = nodupes

 For Each ce In arr
  MsgBox ce
 Next ce

End Sub


Tony
 

jmichaelp

Board Regular
Joined
Aug 18, 2003
Messages
98
Hi Tony, thanks for the quick reply! I was hard at work & haven't had time to look at this until now...

Your solution works perfectly - maybe too perfectly! The data is now in a one-dimensional array so that if you have consecutive values that are the same (e.g. "0"), one of those is left out. For example, on the folowing data here are my results:

ABC
0.05
0
20
2169
60
XYZ
0.1
1
308
DEF
200
8802
55
GHK
0.15
80
1744
LMN
Options version 6s delta neutral news sort DN1 altsource3i new stocks h combine14a Yahoo source temp.xls
ANAOAPAQARASATAU
5ABC0.050.000.000.0520216960
6ABC0.050.000.000.0520216960
7XYZ0.050.000.000.10130860
8XYZ0.050.000.000.10130860
9ABC0.050.000.000.0520216960
10ABC0.050.000.000.0520216960
11XYZ0.050.000.000.10130860
12XYZ0.050.000.000.10130860
13DEF0.050.000.000.05200880255
14DEF0.050.000.000.05200880255
15GHK0.100.000.000.1580174455
16GHK0.100.000.000.1580174455
17LMN0.050.000.000.05200880255
18LMN0.050.000.000.05200880255
Download


What I'd really like is a multi-dimensional array (because a multi-dimensional array is how my program is set up to address the existing data) that looks like this:

ABC 0.05 0.00 0.00 0.05 20 2169
XYZ 0.05 0.00 0.00 0.10 1 308
DEF 0.05 0.00 0.00 0.05 200 8802
GHK 0.10 0.00 0.00 0.15 80 1744
LMN 0.05 0.00 0.00 0.05 200 8802

Is there any way to do it? Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,094
Members
412,441
Latest member
kelethymos
Top