Search Matrix

[By_Sal]

Hello I have a matrix composed as follows.


1-2-3-4-5

6-7-8-9-2

3-7-5-4-2

2-6-7-5-7


In the line there are never two or more identical numbers are always different.


identify the numbers are 2-5, the occurrences are in 1 ° -3 ° -4 °, so they are 3 occurrences, specify no matter where they are, just how many times it happens with the occurrence of the two numbers searched for in the data matrix.


Hello By Sal


Sorry translate

 

[Faseeh]

Hi By_Sal,


Assuming your data in A1:A4 formatted as general, try this:

=COUNTIFS(A1:A4,"*2*",A1:A4,"*5*")


or 


=COUNTIFS(A1:A4,"*"&D1&"*",A1:A4,"*"&E1&"*")

If you want to use Cell refrences, D1 & E1 cotains 2 & 5 respectively.

 

[NARAYANK991]

Hi ,


Do you think this will help ?


http://chandoo.org/forums/topic/finding-a-row-of-numbers-in-any-sequence


Narayan

 

[By_Sal]

Hello Faseeh and Narayank, I had problems with the formulas matrix, they could not bring the result.


I solved with this formula


= IF (SUMPRODUCT ((A2: G2 = $ J $ 1) + (A2: G2 = $ K $ 1)) = 2, 1, 2)


dragging down


where J1 and K1 are the guiding values​​.


this is the macro to cycle through all values ​​guide and return occurrences next to the guiding values​​.


Sub ambo4 ()

Dim & r1, r2 &, x &, count As Range, t, ss


r1 = Cells (Rows.Count, 12). End (xlUp). Row

r2 = Cells (Rows.Count, 1). End (xlUp). Row

Range ("N2: N" & r1). ClearContents

Application.ScreenUpdating = False

September count = Range ("H2: H" & r2)

t = Now

For x = 2 To r1

     Cells (1, 10) = Cells (x, 12)

     Cells (1, 11) = Cells (x, 13)

     ss = WorksheetFunction.CountIf (count, 1)

     Cells (x, 14) = ss

Next

Application.ScreenUpdating = True

MsgBox "Matching and execution time" & Format (Now - t, "HH: MM: SS")

end Sub


By Sal Hello and thank you for the answers.