An array formula, that will take data from two sheets of different ranges

[bines53]

Hi XOR LX ,

I'm so sorry, Your solution is right, the result is 4


Regards

David

 

[bines53]

Hi XOR LX ,

Today I had a very long trip, tired from the night, probably disrupted my concentration.
I stopped on the way to respond, without a computer, is not available to check.

This formula
=INDEX(MMULT((COUNTIF(A6:H6,A7:H7)>=COUNTIF(OFFSET(A7,,TRANSPOSE(COLUMN(A7:H7)-COLUMN(A7)),1,COLUMN(A7:H7)-COLUMN(A7)+1),A7:H7))+0,TRANSPOSE(COLUMN(A7:H7)^0)),1)
Also gives the same result: 4
The formula is built on data that horizontally.
Is impossible to avoid the function ,OFFSET ?


Regards

David

 

[XOR LX]

@bines53

Nice formula! Much better than mine. Is it yours?

Not sure if this can be done without the constructions resulting from OFFSET, though I'll have a think.

Regards

 

[bines53]

Hi XOR LX ,

I searched the archives of the forum, the formula given by Haseeb .

Regards

David

 

[NARAYANK991]

Hi David / XOR LX ,

Now that you have clarified that the result for the following set of numbers :

4 ; 5 ; 8 ; 3 ; 8 ; 5 ; 9 ; 5

5 ; 8 ;8 ; 6 ; 3 ; 3 ; 1 ; 1

should be 4 , everything is clear ; since there are two 8s in the first list , and an equal number in the second list , that counts as 2 matches , which is why the result is 4. If either list had a lesser number , then the number of matches would be based on the lesser number ; thus , both of the following lists would result in the number of matches being 3.

4 ; 5 ; 8 ; 3 ; 8; 5 ; 9 ; 5

5 ; 8 ; 7; 6 ; 3 ; 3 ; 1 ; 1

or

4 ; 5 ; 8 ; 3 ; 7; 5 ; 9 ; 5

5 ; 8 ; 8; 6 ; 3 ; 3 ; 1 ; 1

See the attached file for the revised macro.

Narayan

 

[bines53]

Hi Narayan ,

Now, I can say with certainty, it's perfect !


Regards

David