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.

Any help is appreciated, regards,

Niels

sub 10points()
rw = 1
for i = 1 to 10
for j = 1 to 10
cells(rw,1) = i : cells(rw,2) = j
rw = rw + 1
next
next
end sub

--
Regards,
Tom Ogilvy


"Niels" wrote:

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.

Any help is appreciated, regards,

Niels

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.

Any help is appreciated, regards,

Niels

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

Tom,

I tested your script, but this does not schange the order in the second
column. I already found out that I need a max of 999 combinations (see reply
Bernie Deitrick). So for 4 points the file would have to 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

regards,

Niels

"Tom Ogilvy" wrote in message
...
sub 10points()
rw = 1
for i = 1 to 10
for j = 1 to 10
cells(rw,1) = i : cells(rw,2) = j
rw = rw + 1
next
next
end sub

--
Regards,
Tom Ogilvy


"Niels" wrote:

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.

Any help is appreciated, regards,

Niels

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

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

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

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

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

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

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