I have a set of data, let say start from A1 to F1 = 1,2,3,4,5,6
I need to have a macro to create the output as follow:

A2 to D2 = 1,2,3,4
A3 to D3 = 1,2,3,5
A4 to D4 = 1,2,3,6
A5 to D5 = 1,2,4,5
A6 to D6 = 1,2,4,6
..
..
..
An to Dn = 1,4,5,6
..
..
..
Ax to Dx = 3,4,5,6

Basically, the macro should create any four combination from the data.

Thank you

Hi

Your post implies that you don't want to include combinations where the
order of the original data changes e.g:
1,2,4,3

This is pretty rough but try:

Sub Pop2()
Dim theData(5) As Integer 'change data type to suit values in A1:F1
Dim i As Integer
Dim rct As Long
Dim r1 As Integer
Dim r2 As Integer
Dim r3 As Integer
Dim r4 As Integer

Application.ScreenUpdating = False

For i = 0 To 5
theData(i) = Cells(1, i + 1).Value
Next i
rct = 2
For r1 = 0 To 2
For r2 = 1 To 3
For r3 = 2 To 4
For r4 = 3 To 5
If r1 < r2 And r2 < r3 And r3 < r4 Then
Cells(rct, 1) = theData(r1)
Cells(rct, 2) = theData(r2)
Cells(rct, 3) = theData(r3)
Cells(rct, 4) = theData(r4)
rct = rct + 1
End If
Next
Next
Next
Next

Application.ScreenUpdating = True

End Sub

If you did want to include all combinations of the data including changes to
the order then:

Sub Pop()
Dim theData(5) As Integer 'change data type to suit values in A1:F1
Dim i As Integer
Dim rct As Long
Dim r1 As Integer
Dim r2 As Integer
Dim r3 As Integer
Dim r4 As Integer

Application.ScreenUpdating = False

For i = 0 To 5
theData(i) = Cells(1, i + 1).Value
Next i
rct = 2
For r1 = 0 To 5
For r2 = 0 To 5
For r3 = 0 To 5
For r4 = 0 To 5
If r1 < r2 And r1 < r3 And r1 < r4 _
And r2 < r3 And r2 < r4 _
And r3 < r4 Then

Cells(rct, 1) = theData(r1)
Cells(rct, 2) = theData(r2)
Cells(rct, 3) = theData(r3)
Cells(rct, 4) = theData(r4)
rct = rct + 1
End If
Next
Next
Next
Next

Application.ScreenUpdating = True

End Sub

Hope this helps
Rowan


"broogle" wrote:

I have a set of data, let say start from A1 to F1 = 1,2,3,4,5,6
I need to have a macro to create the output as follow:

A2 to D2 = 1,2,3,4
A3 to D3 = 1,2,3,5
A4 to D4 = 1,2,3,6
A5 to D5 = 1,2,4,5
A6 to D6 = 1,2,4,6
..
..
..
An to Dn = 1,4,5,6
..
..
..
Ax to Dx = 3,4,5,6

Basically, the macro should create any four combination from the data.

Thank you

It works great!

Thanks