find univocal values whit macro

[max_max]

Hi to all,
the attached file finds the repeated values with a formula.
Now the file is with lots of rows and is very slow.
can this be done with a macro?
max_max

[karedog]

Maybe :

Please Login or Register  to view this content.

[max_max]

Hi karedog.
I do not understand how it works.

[karedog]

But is the result is what you expected ?
I use collection object to achieve the result, you may want to google for "vba collection tutorial" keywords.

[max_max]

An explanation of how it works?

[karedog]

1. Get data from column A-B to array a
2. Create array output (array b)
3. Using collection c, create unique items with its key is concatenate of data on column A and B at the same row
4. While looping in step 3 above, add a counter on how many data with same data is found (will be used as column C value)
5. Using collection d, create unique items with its key is data on column A only
6. Create sub collection inside collection d (step 5 above) for each unique items on column B (so step 5 and 6 will create a "Pivot Table" liked structure, will be used as column D value)
7. Collect all information from collection C and D to array b
8. Put array b back into worksheet

[max_max]

Hi Karedog
thanks for your intervention but it seems too complicated.
Can you create a simple macro?
max_max

[karedog]

If we create "simple macro" (just using array looping), it will be slower than your original worksheet function.
The speed is gained using collection, without this object, it is useless to use VBA macro (to make speed improvement) for your case.

[max_max]

Unfortunately translation with google translator is not so understandable.
I do not understand the various steps.