Pull uniques for large amounts of data

[YasserKhalil]

Hello everybody
I have working code that pulls the unique values and it is working very well in this attachment but for real data it is very very slow
so I as seeking to use arrays to do that task
There's my code

Please Login or Register  to view this content.

The code copy column B to Column O
Then >>
in Column P it brings the unique items which exists in column C and doesn't exist in column O
Then >>
in Column Q it brings the unique items which exists in column D and doesn't exist in column O + Column P
Then >>
in Column R it brings the unique items which exists in column E and doesn't exist in column O + Column P + Column Q
and so on
This is the logic

Hope it is clear
You can run the macro to see the desired results

[berlan]

Hi buddy,

See if this works and would speed things up;

Please Login or Register  to view this content.

//berlan

[Kalithro]

Fully agree with berlan, a "Scripting.Dictionary" is the way to go. Well worth taking the time and effort to learn that reference library.

[daffodil11]

This just requires a reference set to Microsoft Scripting Runtime.

Please Login or Register  to view this content.

What's crazy is that I'm still pretty terrible with standard VBA arrays, but the scripting dictionary was easier to start off with. I just like not having to know the limit up front, or ReDim'ing anything for something dynamic.

[YasserKhalil]

Thanks a lot great people Mr. Berlan and Mr. Daffodil
I will try this on the real file and see the results ..
Thank you very much
Regards

[YasserKhalil]

Thank you very much for this gift ..Thanks alot for both of you
YOU ARE AWESOME

[berlan]

@daffodil11, nice work -- short and concise!! With with less interactions between cells and VBA it would come down in speed even more. Maybe consider using 'late binding' for simplicity in the final code so no reference to Microsoft Scripting Runtime is needed

@YasserKhalil, you're welcome, your threads are always worth a challenge! And thanks for the rep added

In terms of speed of running the different procedures on my PC (with the example workbook provided)
- Original code, ~1.6 sec on average
- My code, ~0.03 on average
- daffodil11's code, ~0.2 on average

[xladept]

Hi Guys,

Please Login or Register  to view this content.

* I got .039 with this one

[daffodil11]

I don't know the Late Binding for Scription.Dictionary yet!



Soooooon!

[YasserKhalil]

Thanks a lot for all these wonderful contributions
The time elapsed for each code:
------------------------------
Berlan : 0.328
Daffodil : 0.047
Xladept : 0.438
Me (The worst of course) : I won't mention my scandal

[xladept]

I get .039 for mine .019 if I just clear contents

I just ran Daffodil's in .0078! WOW!

[daffodil11]

What timing model are you folks using?

I usually run X = timer, msgbox Timer - X. Is that normal?

[berlan]

Berlan : 0.328

haha, really??

Using my laptop, I get 0.047 sec at best for yours xladept, 0.031 sec at best with mine, but I can't get the time down with Daffodil's using Excel 2016 -- very strange.

Please Login or Register  to view this content.

[skywriter]

Excel 2016

Didn't even know there was such an animal.

[xladept]

This is how I time routines:

Please Login or Register  to view this content.

[berlan]

Didn't even know there was such an animal.

A preview version of Excel 2016 I should add...



@xladept, thanks for sharing..

[kalak]

Those of you interested in speed for this problem as stated would be better off scrubbing the dictionary approach entirely and trying something like the following:

Please Login or Register  to view this content.

The timing difference should really show up in a more realistic sized data set, say up to 10,000 rows as indicated in the opening post.
Or you could try it on a million rows and see how long the dictionary approach takes. Maybe you'd be waiting for some time.

[YasserKhalil]

Mr. Kalak
You are genius. That's very great solution too
I'm learning a lot from you great guys