Sub Checkcolumns
Dim v1 as Variant, v as Variant
Dim i as Long, rng as Range, cnt as Long
dim cntv(1 to 3) as Long, j as long
Dim k as Long, kk as Long

' enter the values that idicate which trial type
v1 = Array("type1","type2","type3")
' enter corresponding probabilities/proportions
v2 = Array(.35, .25, .40)

k = lbound(v1)

for i = 100 to 1 step -1
set rng = range(cells(1,1),cells(1,1).End(xldown))
v = rng.Value

cnt = 0
for i = 1 to rng.count
j= 0
for kk = k to ubound(v1)
j = j + 1
if v(i,1) = v1(k) then cntv(j) = cntv(j) + 1
exit for
Next
if i < 1 then
if v(i,1) = v(i-1,1) then
cnt = cnt + 1
else
cnt = 1
end if
if cnt = 4 then
columns(i).Delete
exit for
end if
End if
Next
if Abs((cntv(k)/rng.count)-v2(k)) .05 or _
Abs((cntv(k+1)/rng.count)-v2(k+1)) .05 or _
Abs((cntv(k+2)/rng.count)-v2(k+2)) .05 then
columns(i).Delete
end if
Next
End Sub

Untested code, so may contain typos or other errors.

--
Regards,
Tom Ogilvy


"clulesacademic"
wrote in
message news:clulesacademic.22yxwp_1139499605.322@excelfor um-nospam.com...

hi,

i desperately need your help. im trying to generate lists of of 240
experimental trials.

there are three trial types (each trial type has an associated
probability of occurrence).

what i have done so far is i have used the discrete randomisation to
generate 100 lists of 240 trials each (one column for each block, all
blocks on a separate sheet). i have also gotten the macro to calculate
the probability of occurrence of each trial type within each of the 100
generated blocks.

now i need to censor those lists in the following ways:

1. eliminate blocks that have 4 or more of the same trial type in a
row
2. eliminate blocks in which the trial type probability falls outside a
specified range.

any help at all would be hugely appreciated. i am really struggling
here. thank you so much.


--
clulesacademic
------------------------------------------------------------------------
clulesacademic's Profile:
http://www.excelforum.com/member.php...o&userid=31368
View this thread: http://www.excelforum.com/showthread...hreadid=510608