How to identify entries in a matrix also present in another list

[Guest]

I have two matrices of data which have a large number of the same entries. I
need to be able to identify the number of entries in one matrix which also
occur within the same column in the other matrix (e.g. how many of the
entries in column A in one matrix are also in each column of the other
matrix). At the moment the best I can do is use pivot table to search each
column in one matrix against each column in the other, which is not ideal in
that I have about 200 columns in each matrix. It would also be ideal if I
could identify which were the common elements.

 

[flydecoder]

Assuming that each item in the first list occurs only once, but in the
second list occurs variable numbers of times and that both lists are in
column A
In column B on the first list, starting with cell B1
=COUNTIF(Sheet2!A:A,Sheet1!A1) and copy down,

If you have multiple occurances on both lists, but only want to count
the number once on the first list

=IF(COUNTIF($A$1:A1,A1)>1,"",COUNTIF(Sheet2!A:A,Sheet1!A1))
and copy down

Hope this helps

 

[Guest]

I needed more clarification of the problem: Each element will only occur
once in each matrix. I need to know how many elements in column A in Matrix
1 are in each column in Matrix 2, how many elements in column B in Matrix 1
are in each column in matrix 2 etc. eg.
Matrix 1: Matrix 2
A B C A B C
1 2 3 3 1 8
4 5 6 6 7 12
7 8 9 9 10 5

I would need it to tell me that Matrix 1 column A had 2 elements in common
with 2:B
and none in common with 2:A or 2:C, column 1B had 0, 0, 2, column 1C had 3,0,0

I would imagine hte output would be a new matrix:

A 020
B 002
C 300

or something to that effect.

Any suggestions?
Thanks,
JANE