Once worksheet data is in array, how to eliminate duplicates

[jmichaelp]

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!

 

[acw]

Hi

How about something like

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]

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
	AN	AO	AP	AQ	AR	AS	AT	AU
5	ABC	0.05	0.00	0.00	0.05	20	2169	60
6	ABC	0.05	0.00	0.00	0.05	20	2169	60
7	XYZ	0.05	0.00	0.00	0.10	1	308	60
8	XYZ	0.05	0.00	0.00	0.10	1	308	60
9	ABC	0.05	0.00	0.00	0.05	20	2169	60
10	ABC	0.05	0.00	0.00	0.05	20	2169	60
11	XYZ	0.05	0.00	0.00	0.10	1	308	60
12	XYZ	0.05	0.00	0.00	0.10	1	308	60
13	DEF	0.05	0.00	0.00	0.05	200	8802	55
14	DEF	0.05	0.00	0.00	0.05	200	8802	55
15	GHK	0.10	0.00	0.00	0.15	80	1744	55
16	GHK	0.10	0.00	0.00	0.15	80	1744	55
17	LMN	0.05	0.00	0.00	0.05	200	8802	55
18	LMN	0.05	0.00	0.00	0.05	200	8802	55
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.