ExcelBanter - Data series

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