2

I have 300+ rows containing 40+ columns, each column has a number 1-40 at random.

I want to know how to search each row for pairs or numbers that occur together within the row.

Example.

5   10  12  14  16  17  19  30  31  32  33  40  51  
4   7   11  16  17  23  24  26  32  37  39  46  47  
1   2   4   7   11  15  18  31  34  35  37  38  43  
3   6   14  16  19  21  24  27  33  34  38  42  47      
5   7   8   15  17  22  23  24  26  27  30  37  40  

I want to know how many time 1 and 2 occur together or 23 and 24, or 5 and 30.

How would I do this? which formula would I use and how would I implement it.

I've looked at countifs, pivot tables, sumproducts, totals. And I am more confused than when I started.

  • Are you trying to find every combination (1 and 2, 1 and 3, 1 and 4...), does order matter (is 1 2 the same as 2 1) do they actually need to be beside one another or just both occur in the row? – gtwebb May 9 '16 at 23:32
3

You need to combine both IF, AND and COUNTIF.

For example,

=IF(AND(COUNTIF(A1:J1,23)>0,COUNTIF(A1:J1,24)>0),1,0)

This solution yields 1, IF both 23 AND 24 occurred at least once from A1 to J1, and yields 0 if not both of them appear in the row.

Picture: countif a pair exists within a row

2

Because of the nesting involved, I would recommend a User Defined Function (UDF) to solve this.

This code ...

Function CountPairs(theRange As Range, P As Variant, Q As Variant) As Long
Dim PRow As Range
Dim PCell As Range, QCell As Range
Dim Result As Long
' initialize
Result = 0

For Each PRow In theRange.Rows      'search each row for P Value
    For Each PCell In PRow.Cells
        If PCell.Value = P Then
            For Each QCell In PRow.Cells 'if P Value found, search for Q Value
                If QCell.Value = Q Then
                    Result = Result + 1
                    Exit For
                End If
            Next QCell
            Exit For
        End If
    Next PCell
Next PRow

' clean up
Set PRow = Nothing
Set PCell = Nothing
Set QCell = Nothing

CountPairs = Result

End Function

Yields these results ...

enter image description here

enter image description here

... where I used conditional formatting to highlight the "P" and "Q" values


However, with a "helper" column, it can be solved with built in formula ...

enter image description here

Where, in this case the helper column contains this formula filled down ...

=IF(COUNTIF($B4:$N4,"="&$B$2)>0,IF(COUNTIF($B4:$N4,"="&$C$2)>0,1,0),0)

and Cell A2 contains a simple sum ...

=SUM(A4:A8)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.