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!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,999
Messages
5,834,813
Members
430,323
Latest member
Regash

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