Count duplicates in several columns

[YasserKhalil]

Hello everyone

In Sheet1 I have range of values in range("B3:F25") .. and I need to do some processes on these values
Manually I copy this range and put it to range("K3:O25") then sort this new range according to the five columns by column K & L & M & N & O
After that I had to extract the duplicates in the five columns and count them
then to extract the duplicates in four columns and count them
then extract the duplicates in three columns and count them
then extract the duplicates in two columns and count them

I have attached the expected and final desired output in columns S:X and leave three blank rows in between each set of results
Thanks advanced for help

[karedog]

Maybe :

Please Login or Register  to view this content.

[YasserKhalil]

Hello Mr. Karedog
In fact the words would not be enough to thank you .. You are really wonderful and awesome
Thank you very much for this great help

Just need to get these two lines

Please Login or Register  to view this content.

Please Login or Register  to view this content.

And as for the sort process is it important here? I think it is not important ..
I just put it in the sample file so as to show the manual steps that make the work easier (that's all) but as you in wonderful way stored it in array, I think I will do without this part.. Just tell me if I am right or not

Best and kind regards

[YasserKhalil]

Now I used this fantastic code (deleted all the columns that were put for illustration and got the desired results as expected exactly)

Please Login or Register  to view this content.

How can I merge the headers of Duplicates In ... in easier way?

[karedog]

You are welcome. Just add/modify the last part of the code :

Please Login or Register  to view this content.

Regards

[YasserKhalil]

Thank you very much my best tutor ..
Waiting for explaining the two lines I referred to in post #3

[karedog]

Please Login or Register  to view this content.

If we only have one collection, say :

Please Login or Register  to view this content.

then the syntax to add data to the collection would be :

Please Login or Register  to view this content.

But if we have many collections with uniform structures, we can set array where each of its member is a collection.

It is like you have many variables vs you put them in single array like this :
- Many variables :

Please Login or Register  to view this content.

- Using array :

Please Login or Register  to view this content.

The same thing applied to collection, if you have many collections, your code would be like this :
- Many collections :

Please Login or Register  to view this content.

- Using array :

Please Login or Register  to view this content.

---

Please Login or Register  to view this content.

I have once answer your question about collection inside collection (nested).
Collection can store any kind of variables, arrays, and classes.
So you can :

Please Login or Register  to view this content.

If we want to access an array, we will call it through its index, for example :

Please Login or Register  to view this content.

So now we put it all together :
- to access the member of single collection, it would be :

Please Login or Register  to view this content.

- to access the member of arrayed collection, it would be :

Please Login or Register  to view this content.

- If the value stored in ".item" is an array, to access the member of this array would be :

Please Login or Register  to view this content.

where (0), (1), (2), etc, is index of the array, just like "arr = Array(1, 2, 3) --> Debug.print arr(1)"

- So to access the child collection of big the collection, the index to be used is 1 (because it is zero based index, created using "Array(strKey, New Collection)" syntax) :

Please Login or Register  to view this content.

- To add some data to this child collection, the syntax is :

Please Login or Register  to view this content.

[YasserKhalil]

That's marvelous and fascinating .. You are a great tutor and I enjoyed this tutorial a lot
Thank you very very much Mr. Karedog
Best and kind regards

[karedog]

You are welcome Mr.Yasser Khalil, glad I can help.

Regards