CODEWITHSUNDEEP
vba
Max
Max

Reputation: 940

Matching multiple cell values on a table

I am seeking a solution to an excel problem. I am advised to post this question on a VBA forum here, although I thought this would be possible on excel, I am also open to alternative ideas if it can be done easier without excel (e.g html or javascript etc).

Here is my sample image of the excel table. There are triplets of values on left, if these triplets are present altogether on any ROW of the table, it should give a positive output of 1, if not 0.

enter image description here

*** The output should be 1 only if all three values are present on a row together. The values may not be in adjacent cells.

On the excel forum, These were the suggested formulas:

=IF(IF(COUNTIF($F$2:$I$6,"="&A2) >0,1,0) + IF(COUNTIF($F$2:$I$6,"="&B2) >0,1,0) + IF(COUNTIF($F$2:$I$6,"="&C2) >0,1,0) = 3,1,0)

=MIN(1,COUNTIF($F$2:$I$6,A2),COUNTIF($F$2:$I$6,B2),COUNTIF($F$2:$I$6,C2))

which don't answer my problem exactly because they seek and match the values on the whole table.

Note: I don't know VBA.

Upvotes: 1

Views: 229

Answers (1)

3-14159265358979323846264
3-14159265358979323846264

Reputation: 3290

Using the following Worksheet

enter image description here

Add a VBA module, and paste the following function in ...

Function MatchingOnRow(rngFind As Range, rngIn As Range) As Integer
Dim bFound As Boolean
For i = rngIn.Row To rngIn.Row + rngIn.Rows.Count
    For Each rngCheck In rngFind
        bFound = False
        For j = rngIn.Column To rngIn.Column + rngIn.Columns.Count
            If Cells(i, j).Value = rngCheck.Value Then
                bFound = True
                Exit For
            End If
        Next j
        If bFound = False Then Exit For
    Next rngCheck
    If bFound = True Then
        MatchingOnRow = 1
        Exit Function
    End If
Next i
MatchingOnRow = 0
End Function

And then in Column D (row 2) add a formula thus

=MatchingOnRow(A2:C2,$F$2:$I$6)

Enjoy!

Upvotes: 1

Related Questions