I can do it but I don't know why

[troggot]

Hi!

This seems to be repeating topic in my tech life as an engineer, I do things but no idea how.

So my problem is the simplest I guess. How to compare 2 columns and see if there are common values.

I have even resolved somehow by cutting&pasting from here and there and changing values randomly. But I have not understood the sintax of this formula:

=INDEX($A$1:$A$12, SMALL(IF(COUNTIF($B$1:$B$12, $A$1:$A$12), ROW($A$1:$A$12)-MIN(ROW($A$1:$A$12))+1, ""), ROW(A1)))

Would any one be able to describe me in layman terms what I have done?

I add a pic of the result.

Additional question would be: Is there a way to have the number of occurrences rather than the string repeated in column C?
The fact that I don't understand the sintax is not helping me to be flexible enough to change it.

Also, is all that mess really needed?
In the beginning I was looking in this area:

=IF(ISERROR(MATCH(A1:A210,$B$1:$B$33,0)),"",A1)

or

=IFERROR(IF(VLOOKUP(B1,$A:$A,1,FALSE)=B1,"x"),"")



But I have not been able to make it work. Of course because basically I don't understand what I am doing.

Many thanks!

[xor]

See the attached.

[xor]

To count the number of common values you can also use the simpler:

=SUM(--(ISNUMBER(MATCH($A$1:$A$12,$B$1:$B$10,0)))) (array-entered)

MATCH($A$1:$A$12,$B$1:$B$10,0) evaluates to:
{10,#N/A,#N/A,5,#N/A,1,2,#N/A,#N/A,#N/A,#N/A,2}
meaning that the first value in A1:A12 is in the tenth position in B1:B10. The values in the second and third value in A1:A12 are not in B1:B10 and so on.

ISNUMBER(MATCH($A$1:$A$12,$B$1:$B$10,0)) evaluates to:
{TRUE,FALSE,FALSE,TRUE,FALSE,TRUE,TRUE,FALSE,FALSE ,FALSE,FALSE,TRUE}
and by double negation (--) these Boolean values are converted from a lot of TRUE, FALSE's to:
{1,0,0,1,0,1,1,0,0,0,0,1}
which inserted in the SUM function returns 5.

[troggot]

Many thanks for the help and sorry for the delayed answer.
I guess I disabled the automated emails and I still believed no one answered!

Today I checked!