Intersection Problem

[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?

 

[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?

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.

 

[Claus Busch]

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.

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.

 

[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?

Thanks, Claus. I'm not too good with code, but I'll play around with it and give it a shot.

 

[BobA]

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.