Compare numbers in 2 rows and give me a number how many numbers match?

[JSolitude]

Hello everybody,

I'm pounding my head on the following problem.

Suppose I have a sequence of 7 random numbers in a row, each number in the next column.
(I do know how to randomly generate numbers so this is not the problem).

On the next row I have another sequence of 7 random numbers.

For instance:

1 - 12 - 24 - 31 - 37 - 44
3 - 15 - 27 - 37 - 43 - 45

I'm looking for a formula that would give me as a result 1, because in this case when compared row 1 with row 2, the number 37 was generated twice.

If there would be 2 numbers that would be the same, the formula should give as a result 2, and so on.

I'm thinking about the formula 'compare' or 'search' but how do I construct the formula for the above problem

Thanks a bunch in advance.

 

[JoeMo]

One way (Note this is an array formula - confirm with ctrl+shift+enter):
Sheet10

*	A	B	C	D	E	F	G	H
*	*
*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]44[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula H2 {=SUM(IF(COUNTIF(A1:F1,A2:F2),1,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[JSolitude]

Thank you very much JoeMo. Very nice solution to the problem at hand -why did I not think of this, I'm simply lousy at Excel

Lucky forums like these and helpful people like you exist.

 

[JoeMo]

Thank you very much JoeMo. Very nice solution to the problem at hand -why did I not think of this, I'm simply lousy at Excel

Lucky forums like these and helpful people like you exist.

You are welcome - thanks for the reply.