Find missing combinations

Hi everybody,

My excel worksheet has a series of combinations (numbers "1", "2", "3", "4", "5", "6", "7", "8", "9" and "10" combined 4 by 4), but some combinations are missing and I need a macro to identify these missing combinations.

In order to make my problem understandable, the excel file attached shows the missing combinations in "red" (in fact these missing combinations shown in red are not displayed in the actual list).

So, I would like to ask your help to write a macro that shows the missing combinations in columns "H" to "K".

Thank you very much.

John

hi John,

assuming your data are as shown, in your columns C:F with no gaps but with the possible combinations in red not there (or indeed any of the possible combinations missing) then you can try the following code

Sub missing_combos()

Dim dic As Object, q As Variant
Dim a&, b&, c&, d&, k&

Set dic = CreateObject("scripting.dictionary")

q = Range([c2], [f2].End(4))

For a = 1 To UBound(q)
    dic(Join(Array(q(a, 1), q(a, 2), q(a, 3), q(a, 4)), ",")) = 1
Next a

For a = 1 To 10
    For b = a + 1 To 10
        For c = b + 1 To 10
            For d = c + 1 To 10
                If dic(Join(Array(a, b, c, d), ",")) <> 1 Then
                    k = k + 1
                    Cells(k + 1, "h").Resize(, 4) = Array(a, b, c, d)
                End If
            Next d
        Next c
    Next b
Next a

End Sub

Hi kalak,

Your macro works perfectly !

Thank you very much.

John