Matching multiple criteria across multiple columns and rows

[okadike]

Can somebody help? My data set is as displayed below: same sheet and same rows but different columns. Data on the left is 20 rows by 10 columns; data on the right 20 rows by 5 columns. Data distribution is arbitrary in both. The task is to get a function to match a row that has at least 3 numbers in common and 3 criteria as in red. Eg 59,70 and 13 are 3 criteria and 71,8,55 are common between the two data sets on row 6 number 6. The numbers are all random selection. Nothing fixed. 90 45 59 24 4 82 38 85 19 68 44 25 12 18 31 34 20 25 88 11 10 47 65 13 17 30 66 62 32 56 84 56 18 43 7 51 28 89 75 86 35 87 39 67 42 42 90 89 19 87 63 14 12 45 33 20 79 72 35 28 22 69 12 65 19 15 82 38 18 48 6 53 75 41 7 59 8 35 5 45 71 75 70 13 55 58 71 77 8 55 62 82 39 23 49 24 65 36 9 85 78 73 10 17 22 51 41 45 26 74 15 82 29 65 73 35 87 67 79 88 26 74 47 65 32 59 50 4 18 25 12 67 88 56 51 12 19 29 55 41 71 47 28 54 24 80 14 83 69 10 37 47 43 24 65 62 4 34 15 53 81 51 2 88 31 78 25 27 61 31 7 76 51 70 47 27 2 74 37 6 75 80 10 63 50 9 15 85 54 14 5 39 73 43 64 70 33 22 7 68 36 84 53 48 27 51 23 87 45 69 49 87 83 39 41 68 66 15 76 55 77 33 34 3 7 32 66 85 61 36 41 79 5 78 70 29 11 2 8 65 84 90 5 1 44 47 13 41 78 23 42 54 1 25 5 Criteria example 59 70 13 <tbody> </tbody> <strike></strike>

<strike></strike>

<tbody>
</tbody>

 

[tonyyy]

okadike,

You might consider the following...

Function RowNumber(cr1 As Long, cr2 As Long, cr3 As Long)
Dim arr As Variant
Dim r As Long, c1 As Long, c2 As Long
Dim kounter As Long, kounterCr

arr = ActiveSheet.Range("A1:S" & Cells(Rows.Count, "A").End(xlUp).Row).Value
For r = 1 To UBound(arr)
    kounter = 0
    kounterCr = 0
    For c1 = 1 To 10
        If arr(r, c1) = cr1 Or arr(r, c1) = cr2 Or arr(r, c1) = cr3 Then
            kounterCr = kounterCr + 1
        End If
        For c2 = 15 To 19
            If arr(r, c1) = arr(r, c2) Then
                kounter = kounter + 1
            End If
        Next c2
    Next c1
    If kounter > 2 And kounterCr > 2 Then
        RowNumber = r
    End If
Next r
End Function

Cheers,

tonyyy

 

[okadike]

Tonyyy,
Thanks a lot. I am not familiar with coding. Looking at the data I submitted as columns C to L and Q to U, I was looking at a formula I can use. I managed to put together

=IF(IFERROR(INDEX(C1:C20,MATCH(1,((C21=C1:C20)+(C21=D1:D20)+(C21=E1:E20)+(C21=F1:F20)+(C21=G1:G20)+(C21=H1:H20)+(C21=I1:I20)+(C21=J1:J20)+(C21=K1:K20)+(C21=L1:L20))*((D21=C1:C20)+(D21=D1:D20)+(D21=E1:E20)+(D21=F1:F20)+(D21=G1:G20)+(D21=H1:H20)+(D21=I1:I20)+(D21=J1:J20)+(D21=K1:K20)+(D21=L1:L20))*((E21=C1:C20)+(E21=D1:D20)+(E21=E1:E20)+(E21=F1:F20)+(E21=G1:G20)+(E21=H1:H20)+(E21=I1:I20)+(E21=J1:J20)+(E21=K1:K20)+(E21=L1:L20)),0)),0)>0,"YES",0) I was only able to get one part …. but even at that the formula is too cumbersome. I need something simpler and better.. Thanks again

<colgroup><col></colgroup><tbody>
</tbody>

<strike></strike><strike></strike>

 

[Yongle]

One way using a helper column...

With
- criteria in cells A1,B1,C1
- left data range A2:J18
- right data range O2:S18

Put this formula in U2 and copy down

=(COUNTIF(A2:J2,O2)+COUNTIF(A2:J2,P2)+COUNTIF(A2:J2,Q2)+COUNTIF(A2:J2,R2)+COUNTIF(A2:J2,S2)>2)*(COUNTIF(A2:J2,$A$1)+COUNTIF(A2:J2,$B$1)+COUNTIF(A2:J2,$C$1)=3)

And this formula in D1

=MATCH(1,U:U,0)

D1 returns 7 which is the correct row


Assumes both sets of values within each row are unique - duplicate values have not been considered

 

[okadike]

Yongle,
Thanks a lot for your response. However it does not address my situation. Consider it that the 20 x 10 and 10 x 5 data set is a packet. That means, I need a formula to address A2:J21 with O2:S21 together in one cell. Your idea is good for A2:J2 with O2:S2 but not all the data set together. See my effort below at putting everything together...

=IF(IFERROR(INDEX(C1:C20,MATCH(1,((C21=C1:C20)+(C21=D1:D20)+(C21=E1:E20)+(C21=F1:F20)+(C21=G1:G20)+(C21=H1:H20)+(C21=I1: I20)+(C21=J1:J20)+(C21=K1:K20)+(C21=L1:L20))*((D21=C1:C20)+(D21=D1:D20)+(D21=E1:E20)+(D21=F1:F20)+(D21=G1:G20)+(D21=H1:H 20)+(D21=I1:I20)+(D21=J1:J20)+(D21=K1:K20)+(D21=L1:L20))*((E21=C1:C20)+(E21=D1:D20)+(E21=E1:E20)+(E21=F1:F20)+(E21=G1:G2 0)+(E21=H1:H20)+(E21=I1:I20)+(E21=J1:J20)+(E21=K1:K20)+(E21=L1:L20)),0)),0)>0,"YES",0)​

 

[Yongle]

it does not address my situation

I bail out