random database text picks, no or only one duplicate

Posted by JB on July 19, 2000 1:55 PM

Question recently came up at work: want to be able to randomly pick a name from an existing database without exceeding duplicating that name more than twice. I can't even figure out how to completely create the formula without duplicating that name once, let alone twice. So if you have a list of say 6 employees and you want to randomly assign them up to two tasks a piece... I have been playing around with the random and database formulas without any luck. Any comments welcome. Thanks.

Posted by frank on July 20, 0100 1:42 AM

This probably isn't anywhere near what you want but I made up ten randomn numbers from 1 to ten then checked to see if any numbers were duplicated. If any where I calculated the list till they were all dif it takes a while sometimes but it was fun making. Put this in a new workbook and just run it. I probably shouldn't even post this but it was fun.

Sub randtrunc()

Range("C2").Select
ActiveCell.FormulaR1C1 = "=TRUNC(RAND()*10)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C11"), Type:=xlFillDefault
Range("C2:C11").Select

With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
End With
For y = 2 To 10
Cells(y, 3).Select
If Cells(y, 3) = Cells(y + 1, 3) Then
Calculate
y = 1
Else
For x = y To 10
If Cells(y, 3) = Cells(x + 1, 3) Then
Calculate
y = 1
End If
Next x
End If
Next y
Range("C2:C11").Select
Range("C11").Activate
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

Posted by JB on July 21, 0100 11:06 AM

Thanks Frank, anyone else have idea for this?

Thanks Frank, is interesting, perhaps I can tweak it to work for text strings and have it end or end earlier. Not quite sure how to associate it with text (names). Perhaps there is a way to assign numbers to text, so as to display the text while actually calculating on the number.

Posted by Ada on July 21, 0100 8:37 PM

Re: Thanks Frank, anyone else have idea for this?

JB
Just add a column with a number assigned to each name and have the macro work on the column containing the numbers. Also, if you have a total number of names other than 10, just change the code accordingly.
Ada