Intersection Problem

  • Thread starter Thread starter BobA
  • Start date Start date
B

BobA

If I have the following in nine cells:

1 2 3
1 2 4
2 3 4

The number two is in all three columns.

If I have:

1 2 3 5
1 3 6
1 3 7 8 9

The numbers one and three are in all three columns.

Can I write a formula to tell me how many intersections there are in all three columns?
 
If I have the following in nine cells:



1 2 3

1 2 4

2 3 4



The number two is in all three columns.



If I have:



1 2 3 5

1 3 6

1 3 7 8 9



The numbers one and three are in all three columns.



Can I write a formula to tell me how many intersections there are in all three columns?
Click to expand...

I guess I should have said rows. I need to write a formula that tells me how many of the numbers are in all three rows:

1 2 3
1 2 3
1 2 4

The answer here would be two. There is a one and a two in all three rows.
 
Hi Bob,

Am Wed, 6 Aug 2014 12:46:41 -0700 (PDT) schrieb BobA:
1 2 3 5
1 3 6
1 3 7 8 9

The numbers one and three are in all three columns.
Click to expand...

try the following function. Call it in the sheet with
=Occurence(A1:E5) for your example above:

Function Occurence(myRng As Range) As Long
Dim i As Long, j As Long, n As Long
Dim counter As Long, Cnt As Long
Dim arrIn As Variant, arrCheck As Variant, myDic As Object

arrIn = myRng
Set myDic = CreateObject("Scripting.Dictionary")

For i = 1 To myRng.Rows.Count
For j = 1 To myRng.Columns.Count
myDic(arrIn(i, j)) = arrIn(i, j)
Next
Next
arrCheck = myDic.items

For n = 0 To UBound(arrCheck)
counter = 0
If arrCheck(n) <> "" Then
For i = 1 To myRng.Rows.Count
For j = 1 To myRng.Columns.Count
If arrIn(i, j) = arrCheck(n) Then
counter = counter + 1
Exit For
End If
Next
Next
End If
If counter = myRng.Rows.Count Then
Cnt = Cnt + 1
End If
Next
Occurence = Cnt

End Function


Regards
Claus B.
 
If I have the following in nine cells:



1 2 3

1 2 4

2 3 4



The number two is in all three columns.



If I have:



1 2 3 5

1 3 6

1 3 7 8 9



The numbers one and three are in all three columns.



Can I write a formula to tell me how many intersections there are in all three columns?
Click to expand...

Thanks, Claus. I'm not too good with code, but I'll play around with it and give it a shot.
 
Well, Claus I played around with it and after doing a little research I got the code to work. It works perfectly, and I'm actually able to expand the range as well.

Thanks very much for your help, it is much appreciated.
 
Back
Top