Pick a row randomly for each user

sunilbsrv2k

Board Regular
Joined
May 25, 2018
Messages
58
Hi All,

I am having a set of data containing several rows and columns.

This data contains "User Name" in one of the columns; however another column contains the Date on which he accessed a document. One more column has this document ID.

Now, I need to randomly pick the entire row for each of the users but needs to be one row per day per user. It should cover for all the users of that day and paste it in a new sheet.

Request you to kindly help.

Thanks
 

sunilbsrv2k

Board Regular
Joined
May 25, 2018
Messages
58
Hi,

I guess I have complicated my question.

In short, I want my code to pick a row randomly based on the username and the date.

Please help.

Thanks,
 

sunilbsrv2k

Board Regular
Joined
May 25, 2018
Messages
58
Hi All,
I am trying different method to pick a row randomly.

This time, I am filtering the data for each user and trying to pick a row in that. However, I am stuck at the point of copying the row, as the Rnd function is giving me decimal results. Could you please help.

VBA Code:
Sub pickRandomly()
Dim wb, wb1 As Workbook
Dim ws, ws1 As Worksheet
Dim rng, rng1 As Range
Dim i, n As Integer

Set wb = ThisWorkbook

Set ws = wb.Worksheets(1)

Set rng = ws.Range("B10:B17")
Rndm = Rnd(5)
MsgBox Rndm
For Each cell In rng
    'MsgBox cell
    n = 0
    Worksheets("Sheet1").Range("A22").AutoFilter Field:=2, Criteria1:=cell.Value
    i = Application.WorksheetFunction.Subtotal(3, Range("A23:A1000"))
    'MsgBox i
    For n = 1 To i
        If n = Rndm Then
            MsgBox Cells(n + 20, 1).Value
        End If
    Next
    MsgBox n
Next

End Sub
Thanks
 

Forum statistics

Threads
1,078,464
Messages
5,340,460
Members
399,376
Latest member
Tresfjording

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top