Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Pick 5 Random Numbers from each Day

1. I agreed to these rules
Join Date
22nd November 2012
Posts
4

## Pick 5 Random Numbers from each Day

Hi Programmers,

I have a file with 5 days data, where in the data in column B is unique value and the last column have the date.

I have a part of code where in it will ask for the number of random numbers you want ? if i give 5

My requirement is, i need to pick the 5 random values from column B from each single date.

Sample Data file attached. could you please help me with a vba code to get the result. Sample result is also mentioned in the file.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Pick 5 Random Numbers from each Day

I have a part of code where in it will ask for the number of random numbers you want ?
I don't see this code in your sample file. Please provide so that we can amend it with the ask.

3. I agreed to these rules
Join Date
22nd November 2012
Posts
4

## Re: Pick 5 Random Numbers from each Day

Here is the part of my code, which is picking 2 or more random numbers from a same date.
VB:
Sub Random_Values()
Dim Row_Count As Long
Dim HowMany As Long
Dim i As Long
Dim Arr() As String
Dim Msg As String

Windows("Required Data.xlsx").Activate
Sheets("Sheet1").Select
Row_Count = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
HowMany = InputBox("How Many Express #'s You Want to Audit?")
Msg = "Are you Sure, You want to Audit " & HowMany & " Express #'s ?"

ireply = MsgBox(Msg, vbQuestion + vbYesNo)
Case vbYes
Redim Arr(1 To Row_Count)
For i = 1 To Row_Count
Arr(i) = ActiveSheet.Cells(i, 2).Value
Next i
Call Scramble(Arr)
For i = 1 To HowMany
ActiveCell.Offset(i, 7).Value = Arr(i)
Next i
Call Audit_Data
Case vbNo
Call Random_Values
End Select
End Sub
Public Sub Scramble(InOut() As String)
Dim i As Long, j As Long
Dim Temp

Redim ExpressNos(LBound(InOut) To UBound(InOut)) As Double

Randomize
For i = LBound(ExpressNos) To UBound(ExpressNos)
ExpressNos(i) = Rnd()
Next

j = (UBound(ExpressNos) - LBound(ExpressNos) + 1) / 2

Do While j > 0
For i = LBound(ExpressNos) To UBound(ExpressNos) - j
If ExpressNos(i) > ExpressNos(i + j) Then
Temp = ExpressNos(i)
ExpressNos(i) = ExpressNos(i + j)
ExpressNos(i + j) = Temp
Temp = InOut(i)
InOut(i) = InOut(i + j)
InOut(i + j) = Temp
End If
Next i

For i = UBound(ExpressNos) - j To LBound(ExpressNos) Step -1
If ExpressNos(i) > ExpressNos(i + j) Then
Temp = ExpressNos(i)
ExpressNos(i) = ExpressNos(i + j)
ExpressNos(i + j) = Temp
Temp = InOut(i)
InOut(i) = InOut(i + j)
InOut(i + j) = Temp
End If
Next i
j = j / 2
Loop
End Sub

Excel Video Tutorials / Excel Dashboards Reports

4. I agreed to these rules
Join Date
22nd November 2012
Posts
4

## Re: Pick 5 Random Numbers from each Day

Hi Alan,

Any Luck Here ?

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
22nd November 2012
Posts
4

## Re: Pick 5 Random Numbers from each Day

Hi All,

I have made a code for this query and it got executed successfully.

Thanks for your time. But, I would suggest all the programmer here to know the importance of the query and try to reply at least to the thread, so that we will be assuming that someone is looking into it.

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Pick 5 Random Numbers from each Day

This is an all volunteer site. No one here gets paid. We answer questions when we have time. If you have an urgent request, then you should seek professional paid help. We work in all time zones and while you may be awake, a person interested in your issue may be working or asleep. Patience is of the utmost importance when posting to this site. Remember the advise and help you get here is free and if time is of the essence this may not be the place to seek help.

We look forward to your continued participation in this forum.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno