Hi,

Can anyone help me out. I am generating a random list from a larger
list range. My problem is that I it cannot duplicate the first two
characters in the list. For example: a portion of my list looks like:

55-GWP44934A
55-GWP45138A
WD-CLP35803N
WD-CLP97487G
WD-CLP97700G
WD-GWP19171D
WD-GWP27830C
WE-GWP27974C
WE-GWP28039C
WI-GWP45072A
WI-GWP45080A
WI-GWP45081A
WI-GWP45166A
WI-GWP45173A
WJ-CLP93221H
WJ-CLP97539G
WJ-CLP97541G

My formula that is generating a random selection is:
=INDEX(MRO!$C$2:$C$49400,INT((RAND()*UW!$C$56)+1))

I am getting a return value but what I need is for it not to give me
more than one based on the first two characters of the list. Does that
make sense. For example: I cannot have it return me two values that
both begin with WI. Can anyone help me out?

The following is not a formula but VBA code.
I hope that can help you out.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


'Finds and lists random items from the selection.
'The first two characters in each item must be unique.
'The list is added to the immediate right of the selection.
'Requesting more random items then are available causes the
'code to run without stopping - press the escape key.

Sub WinSomeLoseSome()
'Jim Cone - San Francisco, USA - April 2007
On Error GoTo ErrorSomplace
Dim i As Long
Dim j As Long
Dim N As Long
Dim lngSize As Long
Dim M As Variant
Dim vRng As Variant
Dim arrCheck() As String
Dim arrList() As String
Dim strChars As String

Set vRng = Selection
If vRng.Columns.Count 1 Then
MsgBox "Select only one column", , "First Two Only"
Exit Sub
ElseIf Application.CountA(vRng.Offset(0, 1).Cells) 0 Then
If MsgBox("The random list will overwrite column " & _
vRng.Offset(0, 1).Column & ". " & vbCr & _
"Continue?", vbYesNo, "First Two Only") = vbNo Then
Exit Sub
Else
vRng.Offset(0, 1).ClearContents
End If
End If
lngSize = vRng.Count
M = Application.InputBox("How many random items?", _
"First Two Only", 5, , , , , 1)
If M = False Then
Exit Sub
ElseIf M lngSize \ 3 Then
'Number of random items limited to 1/3 of total items.
'Adjust as necessary...
MsgBox "Too many random items requested. ", , "First Two Only"
Exit Sub
End If

Application.ScreenUpdating = False
ReDim arrCheck(1 To lngSize)
ReDim arrList(1 To M)
j = 1

Randomize
Do While j < (M + 1)
'Get a random number
N = Int(Rnd * lngSize + 1)
'Get first two characters of the random pick.
strChars = Left$(vRng(N), 2)
'If already picked loop will exit early.
For i = 1 To lngSize
If arrCheck(i) = strChars Then Exit For
Next 'i
'If not a duplicate...
If i lngSize Then
'Loop did not find a duplicate.
arrList(j) = vRng(N)
arrCheck(N) = strChars
j = j + 1
End If
Loop
'Change from horizontal array to vertical array and
'add random items to the adjacent column.
Range(Cells(vRng.Row, vRng.Offset(0, 1).Column), _
Cells(M + vRng.Row - 1, vRng.Offset(0, 1).Column)).Value = _
Application.Transpose(arrList())
Application.ScreenUpdating = True
Exit Sub

ErrorSomplace:
Beep
End Sub
'-----------



wrote in message
Hi,
Can anyone help me out. I am generating a random list from a larger
list range. My problem is that I it cannot duplicate the first two
characters in the list. For example: a portion of my list looks like:
55-GWP44934A
55-GWP45138A
WD-CLP35803N
WD-CLP97487G
WD-CLP97700G
WD-GWP19171D
WD-GWP27830C
WE-GWP27974C
WE-GWP28039C
WI-GWP45072A
WI-GWP45080A
WI-GWP45081A
WI-GWP45166A
WI-GWP45173A
WJ-CLP93221H
WJ-CLP97539G
WJ-CLP97541G
My formula that is generating a random selection is:
=INDEX(MRO!$C$2:$C$49400,INT((RAND()*UW!$C$56)+1))
I am getting a return value but what I need is for it not to give me
more than one based on the first two characters of the list. Does that
make sense. For example: I cannot have it return me two values that
both begin with WI. Can anyone help me out?

Hello,

One step-by-step approach:
In a new worksheet enter into A2:
=LEFT(MRO!$C2,2)
and copy down to row 49400.

Select cells B2:C1000 (assuming that 999 exceeds your count of
different prefixes) and array-enter
=lfreq(A2:A49400)

Array-enter into D2:D31 (assuming that you have exactly 54 different
prefixes and that you want to get 30 random values)
=INDEX(B1:B55,uniqrandint(55))
and copy down to row 55.

Enter into E2
=INT(RAND()*MATCH(D2,$B$2:$C$31,2,FALSE)+1)
and copy down to E31

Finally enter into F2
=INDEX(MRO!$C$2:$C$49400,MATCH(D2,$B$2:$B$49400,FA LSE)+E2-1)
and copy down to F31

My UDF's lfreq and uniqrandint you find at www.sulprobil.com.

If you need help to identify the count of different prefixes
automatically let me know.

HTH,
Bernd