Niels,

No, that isn't possible - with 999 sets of 512 numbers, you would end up with 1/2 million+ rows....
And you would never reach a place where the last 256 numbers are any different.

Perhaps a better approach would be to randomize the set of numbers and create a specific number of
sets of random splits.

HTH,
Bernie
MS Excel MVP


"Niels" wrote in message ...
Bernie,

Looks very nice, only one thing on the 999. Now it stops at 999 records but the idea was that it
stops at 999 splits of the data. I hope this is not to difficult to change. Thanks,

Niels

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Niels,

Try the macro below with a blank sheet. Works for numbers 8 and up...

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim i4 As Integer
Dim k As Integer
Dim Hi As Integer
Dim mySet As Integer
Const myCnt As Integer = 999

Hi = Application.InputBox("Enter number", Type:=1)

Range("A:B").Clear
Range("A1").Value = "Set"
Range("B1").Value = "Value"

mySet = 1

For i1 = 1 To Hi - 3
For i2 = i1 + 1 To Hi - 2
For i3 = i2 + 1 To Hi - 1
For i4 = i3 + 1 To Hi
Cells(Rows.Count, 2).End(xlUp)(2).Value = i1
Cells(Rows.Count, 2).End(xlUp)(2).Value = i2
Cells(Rows.Count, 2).End(xlUp)(2).Value = i3
Cells(Rows.Count, 2).End(xlUp)(2).Value = i4
For k = 1 To Hi
If k < i1 And k < i2 And k < i3 And k < i4 Then
Cells(Rows.Count, 2).End(xlUp)(2).Value = k
End If
If Cells(Rows.Count, 2).End(xlUp).Row = myCnt Then
Range(Cells(Rows.Count, 1).End(xlUp)(2), _
Cells(Rows.Count, 2).End(xlUp)(1, 0)).Value = mySet
Exit Sub
End If
Next k
Range(Cells(Rows.Count, 1).End(xlUp)(2), _
Cells(Rows.Count, 2).End(xlUp)(1, 0)).Value = mySet
mySet = mySet + 1
Next i4
Next i3
Next i2
Next i1
End Sub


"Niels" wrote in message
...
Bernie,

1, 2, 3, 4 is fine, I only need the split,

Niels

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Niels,

I'm sorry - one more clarification. I mixed up my examples, and really shouldn't have included
the second group that you included in your reply - that example also uses order. Do you want
to see both

1, 2, 3, 4
and
2, 1, 3, 4

or just

1, 2, 3, 4

so that the complete set would be

1, 2, 3, 4
1, 3, 2, 4
1, 4, 2, 3
2, 3, 1, 4
2, 4, 1, 3
3, 4, 1, 2


Sorry about that,
Bernie
MS Excel MVP


"Niels" wrote in message
...
Only grouping (1st half/2nd half) matters to a max. of 999 combinations, so:

1, 2, 3, 4
1, 3, 2, 4
1, 4, 2, 3
2, 1, 3, 4
2, 3, 1, 4
2, 4, 1, 3
3, 1, 2, 4
3, 2, 1, 4
3, 4, 1, 2
4, 1, 2, 3
4, 2, 1, 3
4, 3, 1, 2

Niels

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Does order matter? Because if it does, you could really have:

1, 2, 3, 4
1, 2, 4, 3
1, 3, 2, 4
1, 3, 4, 2
1, 4, 2, 3
1, 4, 3, 2
2, 1, 3, 4
2, 1, 4, 3
2, 3, 1, 4
2, 3, 4, 1
2, 4, 1, 3
2, 4, 3, 1
3, 1, 2, 4
3, 1, 4, 2
3, 2, 1, 4
3, 2, 4, 1
3, 4, 1, 2
3, 4, 2, 1
4, 1, 2, 3
4, 1, 3, 2
4, 2, 1, 3
4, 2, 3, 1
4, 3, 1, 2
4, 3, 2, 1



Or does only grouping (1st half/2nd half) matter? In which case:

1, 2, 3, 4
1, 3, 2, 4
1, 4, 2, 3
2, 1, 3, 4
2, 3, 1, 4
2, 4, 1, 3
3, 1, 2, 4
3, 2, 1, 4
3, 4, 1, 2
4, 1, 2, 3
4, 2, 1, 3
4, 3, 1, 2

Or does only pairing and order matter? In which case:

1, 2, 3, 4
1, 3, 2, 4
1, 4, 2, 3
3, 4, 1, 2
2, 4, 1, 3
2, 3, 1, 4

Or does only pairing matter? In which case:

1, 2, 3, 4
1, 3, 2, 4
1, 4, 2, 3

Bernie
MS Excel MVP


"Niels" wrote in message
...
Bernie,

Thanks for the tip, so it seems that I have to set the upper limit to 999 combinations. But,
the below mentioned formula just gives the number of posible combinations, but I also need
the file described below. It is used in modeling software which uses the first 50% for
model building and the second 50% for testing. Once I have all combinations written out I
use lookup functions to fill the values. So for the 4 points you describe below it would
look like this:

1 1
1 2
1 3
1 4
2 1
2 3
2 2
2 4
3 1
3 4
3 2
3 3

Can I easily produce such file with a threshold of max 999 combinations? Regards,

Niels

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Niels,

=COMBIN(#,#/2)/2

So, for 4 points, there are 3 unique splits:

1 2 3 4
1 3 2 4
1 4 2 3

unless order is important, then use

=COMBIN(#,#/2)

But note that there are
2.36x10^152
unique ways to split 512 points, so doubt you'll be able to deal with all possible
combinations.

HTH,
Bernie
MS Excel MVP


"Niels" wrote in message
...
For modeling purposes in need all posible combination to split 8-512 points in half (50%
training, 50% testing). So for instance 10 points, how many times can I make unique
combinations of 5 by 5. The result file should have in the fist column the number of the
combination, and in the second the order e.g.

1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
2 10
2 1
2 2
2 3
2 4
2 5
2 6
2 7
2 8
2 9
3
.
etc. till max. 999

Any help is appreciated, regards,

Niels