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!

Attached Images

eureka.jpg (137.0 KB, 36 views)

See the attached.

Attached Files

engineer.xlsx (10.7 KB, 19 views)

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.

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!