Hi. Here's just an idea if interested.
I would do a search of these newsgroups for programs that do "Subsets" (ie
of size 2, 3, etc).
There are all kinds of techniques, so pick one you like.
I would break the problem down into 4 steps
Grab each row of data.
Sort that data (so 1,2 and 2,1 are the same)
Call Subset Program
Dump this data into a totals program.
Here's a general idea if interested.
In the vba editor, set a Tools | Reference to the library below.
One of the many, many terrible things about Excel 2007 is that Microsoft
Help system removed Methods and Properties, so It's almost impossible to
study new ideas.
Therefore, set the library ref to help a little via auto complete.
This is just a quick way to count subsets of size 2 combined.
Dim Dic As Dictionary
' = = = = =
' Best w/ Ref to "Microsoft Scripting Runtime"
' = = = = =
Dim Dic As New Dictionary
Dim M As Variant '(M)atrix
Dim r As Long '(R)ow
Dim j As Long
Dim k As Long
Dim Key As String
Const Comma As String = ","
M = [A1:F2].Value
'M = [A1].CurrentRegion.Value
For r = 1 To UBound(M, 1)
For j = 1 To 5
For k = j + 1 To 6
Key = Join(Array(M(r, j), M(r, k)), Comma)
If Dic.Exists(Key) Then
Dic.Item(Key) = Dic.Item(Key) + 1
Dic.Add Key, 1
Next k, j, r
Range("H1:I1").Resize(Dic.Count) = _
' Sort here if desired