i have this small (is it?) problem: in Sheet1 I have in column A,
starting with A3 weekdays names (mon, tue, wed, .., fri, sat, sun),
that means i have 7 rows. In the next 30 columns, on the row 1 I have,
1,2,3 to 30(starting from cell B1, ending AE1). The row 2 contains the
name of weekdays from monday to sunday, repeating till the end of
those 30 columns. In the column AF(starting with AF3) i have numbers,
in order: AF3=2,AF4=1, AF5=0, AF6=2,AF7=1,AF8=0,AF9=1. I have to
distribute these numbers in the blank cells between column A and
column AF random after this example: for row 3 i have to put random
value 1 in every cell that has above it "mon" so that the sum of the
values in the range B3:AE3 to be equal with the value "2" from the
cell AF3. Do you think you can help me?
Thank you!

in cell B3:
=$AF3/countif($B$2:$AE$2,$A3)

and copy this to fill the table.


"hulub" wrote:

i have this small (is it?) problem: in Sheet1 I have in column A,
starting with A3 weekdays names (mon, tue, wed, .., fri, sat, sun),
that means i have 7 rows. In the next 30 columns, on the row 1 I have,
1,2,3 to 30(starting from cell B1, ending AE1). The row 2 contains the
name of weekdays from monday to sunday, repeating till the end of
those 30 columns. In the column AF(starting with AF3) i have numbers,
in order: AF3=2,AF4=1, AF5=0, AF6=2,AF7=1,AF8=0,AF9=1. I have to
distribute these numbers in the blank cells between column A and
column AF random after this example: for row 3 i have to put random
value 1 in every cell that has above it "mon" so that the sum of the
values in the range B3:AE3 to be equal with the value "2" from the
cell AF3. Do you think you can help me?
Thank you!

Since they have to sum to 1 and all the numbers are positive, then I guess
you would only enter 2 ones in all the cells. Is that correct? the only
randomness would be which two Mon get the values.

Also, the Monday the left (column A) dictates that the cells would only
contain 1 and the 1 would be found under Monday.

Would tuesday (row 4) dictate that the cells would contain only 2 and would
be under Tuesday

for row 5, since the sum is 0, no numbers would be entered?

--
Regards,
Tom Ogilvy

"hulub" wrote in message
m...
i have this small (is it?) problem: in Sheet1 I have in column A,
starting with A3 weekdays names (mon, tue, wed, .., fri, sat, sun),
that means i have 7 rows. In the next 30 columns, on the row 1 I have,
1,2,3 to 30(starting from cell B1, ending AE1). The row 2 contains the
name of weekdays from monday to sunday, repeating till the end of
those 30 columns. In the column AF(starting with AF3) i have numbers,
in order: AF3=2,AF4=1, AF5=0, AF6=2,AF7=1,AF8=0,AF9=1. I have to
distribute these numbers in the blank cells between column A and
column AF random after this example: for row 3 i have to put random
value 1 in every cell that has above it "mon" so that the sum of the
values in the range B3:AE3 to be equal with the value "2" from the
cell AF3. Do you think you can help me?
Thank you!

I guess I misinterpreted your question. Are you saying that the numbers in
B3:AE9 must be "1"? And so you want to randomly pick which columns/cells get
the "1"s, picking a number of cells equal to the value in column AF?

"hulub" wrote:

i have this small (is it?) problem: in Sheet1 I have in column A,
starting with A3 weekdays names (mon, tue, wed, .., fri, sat, sun),
that means i have 7 rows. In the next 30 columns, on the row 1 I have,
1,2,3 to 30(starting from cell B1, ending AE1). The row 2 contains the
name of weekdays from monday to sunday, repeating till the end of
those 30 columns. In the column AF(starting with AF3) i have numbers,
in order: AF3=2,AF4=1, AF5=0, AF6=2,AF7=1,AF8=0,AF9=1. I have to
distribute these numbers in the blank cells between column A and
column AF random after this example: for row 3 i have to put random
value 1 in every cell that has above it "mon" so that the sum of the
values in the range B3:AE3 to be equal with the value "2" from the
cell AF3. Do you think you can help me?
Thank you!

Assuming Tom read Hulub right I suggest to enter my
function UniqRandInt() from
http://www.sulprobil.com/html/random_numbers.html
(substitute RandInt by UniqRandInt in line 25, I have to
correct that next weekend) and enter into your spreadsheet:

Cell AG3: =COUNTIF($B$2:$AE$2,$A3)
Cells AH3 and AI3 together (as array formula, enter with
CTRL+SHIFT+ENTER): =uniqrandint(AG3)
Cell B3: =IF($A3=B$2,IF(ISERROR(MATCH(COUNTIF
($B$2:B$2,$A3),OFFSET($AH3,0,0,1,$AF3),FALSE)),0,1 ),0)

Then copy AG3:AI3 down to line 9 and B3 right to AE3 and
finally B3:AE3 down to line 9.

Regards,
sulprobil

you did understand correctly! That's what I have to do.
the only think that i have to choose randomly is the cell in wich i put
the value "1". And I have tried something!
Here is the code:
Sub ceva2()
Dim rng(1 To 9, 1 To 33) As Range, i, j, k, l, m, suma(9) As Integer,
rng1(9) As Range
For i = 1 To 9
For j = 1 To 33
Set rng(i, j) = Cells(i, j)
Next j
Next i
For m = 1 To 9
Set rng1(m) = Me.Range(Cells(m, 2), Cells(m, 31))
Next m
rng1(4).Select
For k = 3 To 9
Do
For l = 2 To 30
If rng(k, 1) = rng(2, l) Then
rng(k, l) = Fix(2 * Rnd())
End If
suma(k) = WorksheetFunction.Sum(rng1(k))
Me.Cells(k, 36).Value = suma(k)
If suma(k) < Cells(k, 32) Then
rng1(k).Clear
End If
Next l
Loop Until suma(k) = Cells(k, 32)
Next k
End Sub
the problem with this code is that in never ends. I waited today 20 min
to end...but nothing happend!
You can tell me what's wrong?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!