Combinations VBA

[Caribeiro77]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TEAM 1[/TD]
[TD]TEAM 1[/TD]
[TD]TEAM 2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TEAM 2[/TD]
[TD]TEAM 1[/TD]
[TD]TEAM 3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TEAM 3[/TD]
[TD]TEAM 1[/TD]
[TD]TEAM 4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]TEAM 4[/TD]
[TD]TEAM 2[/TD]
[TD]TEAM 3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]TEAM 2[/TD]
[TD]TEAM 4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]TEAM 3[/TD]
[TD]TEAM 4[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hy all,

i have in column A, (starting in A2) the above:

I now need all the combinations of games ( just 1 round) E.G each team only plays one time with another team.

Output result in table above

Thanks in advance

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG23May19
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Range("A2:A5")
c = 1
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] nn = n + 1 To UBound(Ray, 1)
        c = c + 1
        Cells(c, "B") = Ray(n, 1)
        Cells(c, "C") = Ray(nn, 1)
    [COLOR="Navy"]Next[/COLOR] nn
 [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Caribeiro77]

Thanks MickG, is perfect.

 

[MickG]

You're welcome