Count unique from three columns

[YasserKhalil]

Hello everyone
I have two sheets where I would like to count the unique items from both sheets (I mean if the same data from three columns from both sheets are the same then this would be counted as just 1)
But if the same data repeated with no equivalent in the other sheet then this would be counted as new 1

To clarify the issue I have copied the data from both sheets (Repair & Check) and put the data in sheet Report in columns E to O then rearrange data so as to make visual analysis of the existing data and the expected count put in column O
Finally I would like to get the same result as in Report column B

Thanks advanced for help

[Marc L]

Hi !

As you already know how to use a dictionary …

[YasserKhalil]

The issue is that the data is in two sheets and the data should be compared across three columns (in real file the columns are non-adjacent)
And the count should be once if the item exists once in both sheets
but if the same item repeated in one sheet again it will be counted again
Please have a look at the Report sheet ..

[karedog]

Maybe :

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very much my friend. That's really amazing and very quick
Can I get the results in column B only as the column A will be input and the site numbers will not be in that order ..so I just need the results in column B based on the input in column A?
And I will study the code carefully so as to make use of it and so as to apply it on the original file which is different somewhat in structure

[karedog]

You are welcome, thanks for reps.
You mean a case where "Site no" is not integer, but other data types like string ?

Then :

Please Login or Register  to view this content.

[YasserKhalil]

Thanks again for your great efforts and the second one is more perfect
What I need is not to get the first column by the code itself .. the first column which is the site no will be user's input and the second result which is the total is the required by the code
So the user may input just three sites of all the sites then he would run the code to get the count of those three sites only
I can use the code to a temporary range then using Vlookup ... but I am seeking for the best all the time as you know
Thank you very much

[karedog]

Should be :

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very much for amazing solutions. Thanks a lot for great help all the time.
Best and Kind Regards my friend

[karedog]

You are welcome, thanks for marking the thread as solved.

Regards

[YasserKhalil]

In this line

Please Login or Register  to view this content.

You left the first two elements empty. Is that necessary to the code as I am trying to study the code?

[karedog]

Not necessary, I just add those 2 empty elements, so the index (column position) of array c will be same (no need to do another mathematic computation) with the examined sheet.

You can also do it like this (without empty elements) :

Please Login or Register  to view this content.

You see, at the last red line you need to add the index by 2

[YasserKhalil]

Thanks a lot for your guide and forgive me if I would ask more questions as your codes is considered as big resource for learning.

[YasserKhalil]

Sorry for disturbing you again. I have applied the code to the following file but it seems that the result is incorrect as for example site 1 in Test sheet is 712 and in Repair sheet is 151 and the result is just 151
Can you please have a look?

[karedog]

Because your code on this line :

Please Login or Register  to view this content.

should be :

Please Login or Register  to view this content.

[YasserKhalil]

That's great. sorry for this bug. That was my fault (I didn't pay attention to that although I have reviewed the code several time)
Regards

[karedog]

Not to worry about it.

Regards