Generate every combination from three columns.

Hello all,

I am wanting to get a list of every possible combination of the 3 x cols A, B, C.
I know there will be thousands of them.

In cols F, G, H I have started with numbers 1 and 2 only, this is only the start and just a sample of what I'm needing.
It would be fine if the results went to sheet 2.

The resulting combinations must be in three columns like cols F, G, and H.
All combinations will come from the range B2:D26 .

Thanks.

15,625 combinations

Thanks for that Trevor.
Just realized as per usual I didn't supply entire correct question to the forum.
There will be blanks cells as well, but the data always starts from col A, meaning as example col A might contain number 12 and cols B and D are blank.
Or Col A and B contain text or numbers and col C is blank. If col C has data, then there is always data in cols A and B. If col B has data, then there is always data in col A.

Hope that makes sense.

Please Login or Register  to view this content.

Thanks again Trevor for your perfect result, I forgot to mention my post three in my 1st post as mentioned, but I have done a single col A and two cols(A&B) manually.
All is good now. Thank you.

You're welcome. Thanks for the rep.

Hello Trevor, back again, I forgot to say that cols C and or B can be blank.

So, if both cols B and C are blank then results can only be cells B2 to B26. I would like the macro to result these as well.
Also, if col C was blank then there would be results for two cols, A and B. I would like to result these as well.

Hope this makes sense.
Thanks.

I have to be honest and say that I don't really understand most of the comments and riders.

From what I can remember, it is a fairly straightforward triple loop. Where are the blanks? If your sample does not represent the reality, please provide one that does.

Ensure that you also include expected results.

Suppose that you want to create unique distinct combinations from A-B-C (or A-B, or A only).
Give it a try

PHP Code: 

Option Explicit
Sub TEST()
Dim lr&, k&, i1&, i2&, i3&, rng, res(1 To 1000000, 1 To 3)
Dim dic As Object, st$
Set dic = CreateObject("Scripting.Dictionary")
lr = Range("A1").CurrentRegion.Rows.Count
rng = Range("B2:D" & lr).Value
For i1 = 1 To UBound(rng)
    For i2 = 1 To UBound(rng)
        For i3 = 1 To UBound(rng)
            st = rng(i1, 1) & "|" & rng(i2, 2) & "|" & rng(i3, 3)
            If Not dic.exists(st) Then
                dic.Add st, ""
                k = k + 1: res(k, 1) = rng(i1, 1)
                If rng(i2, 2) <> "" Then res(k, 2) = rng(i2, 2)
                If rng(i3, 3) <> "" Then res(k, 3) = rng(i3, 3)
            End If
        Next
    Next
Next
Sheets("Sheet2").Activate
Range("A2:C1000000").ClearContents
Range("A2").Resize(k, 3).Value = res
Set dic = Nothing
End Sub 


Thanks Quang,
I have removed all of col C data in this sheet.
So, what changes would I make to result all combinations now.
Thank you.

Nothing change
Just remove C, or B and C, then run the code

Thats great, problem now solved. Thanks for your help, Quang.
Apologies to Trevor for not explaining properly.

No problem. Thanks for the rep