Combinations and Simulations Help

[DyingIsis]

Hello -

I have a set of 39 datapoints and I would like to divide the datapoints into 2 groups. I would like to simulate randomly assigning the datapoints into either group (1 group of 20 and 1 group of 19) 1,000 times.

Is there a way to do this in excel?

Thanks for your help.

- DyingIsis

 

[sulakvea]

hi, something like this maybe. create 2 sheets named "dummy" and "res". in dummy sheet, A1:A39, enter your 39 values. run this code. the results will be stored in "Res" sheet (39x1000 table)

use 20 columns on the left as one set, and the remaining 19 columns as the other set.

keep one thing in mind - there may be duplicate (identical) rows in the table. unlikely though. and even if they exist, wont be more than a couple. the code will need to be changed to handle that, dont wanna do it unless it is necessary.

Option Base 1
Option Explicit

Sub randmun()
Randomize

Dim i As Integer, j As Integer
Dim TmpNum As Integer, TmpDigit As Integer
Dim NumArray(39)

Application.ScreenUpdating = False

For i = 1 To 39
 NumArray(i) = Sheets("dummy").Cells(i, 1).Value
Next i

For j = 1 To 1000

 For i = 1 To 39
  TmpNum = NumArray(i)
  TmpDigit = Int((39 * Rnd) + 1)
  NumArray(i) = NumArray(TmpDigit)
  NumArray(TmpDigit) = TmpNum
 Next i

 For i = 1 To 39
  Sheets("res").Cells(j, i).Value = NumArray(i)
 Next i

Next j

Application.ScreenUpdating = True

End Sub

 

[DyingIsis]

Hello -

Thanks so much.

I was able to implement the VBA, but the table it returned included values that are not within my original set of 39 datapoints.

I guess I mean I would like to randomize the 39 values (datapoints) into 2 groups and simulate that 1,000 times. I would not expect to see any values within the simulation that are not part of the original 39 values.

Please help.

Thanks for your time and effort.

- DyingIsis

 

[sulakvea]

i think i am missing something - with this code you wont see any numbers besides the original 39. first thing the code does is reads the list from "dummy" sheet and works of that list only. it's impossible for the code to return any other value. unless again i am not understanding the question correctly

 

[sulakvea]

here is what i get when i try it. 1000 rows of original 39 numbers in random order.

 

[DyingIsis]

Hi Sulakvea -

Thanks so much for following-up.

Not sure what's making the difference. Is it because my datapoints includes 2 decimal places, so the Macro is rounding all of the numbers up?

If so, is there a way to fix it?

Thanks again for all of your help.

- DyingIsis