Excel automated random selection

[jaz1240]

I have table that has repeated timestamps (multiple records within 1 second). there are a variable number of records within each second; ranging from 1 record to 103 records within a second. I wish to develop a method to select only one random record from all the records with the same timestamp.

The Time column is column A. if possible, i would like the end result of the formula(s) (or macro/VBA) to have the word Keep in column B in the row that was randomly selected. all other non selected rows can be left blank.

thanks and i apologize if my example data table is not formatted correctly

Time

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:05

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:06

7:51:07

7:51:07

7:51:07

7:51:07

7:51:07

7:51:07

7:51:07

7:51:07

7:51:07

7:51:07

7:51:07

7:51:07

7:51:07

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:08

7:51:09

7:51:09

7:51:09

7:51:09

7:51:09

7:51:09

7:51:09

7:51:09

7:51:09

7:51:09

7:51:09

7:51:09

7:51:10

7:51:10

7:51:11

7:51:11

7:51:11

7:51:11

7:51:11

7:51:11

7:51:12

7:51:12

7:51:13

7:51:13

7:51:13

7:51:13

7:51:13

7:51:13

7:51:14

7:51:14

7:51:15

7:51:15

7:51:15

7:51:15

7:51:15

7:51:15

7:51:16

7:51:16

7:51:17

7:51:17

7:51:17

7:51:17

7:51:17

7:51:17

7:51:18

7:51:18

7:51:19

7:51:19

7:51:19

7:51:19

7:51:19

7:51:19

7:51:20

7:51:20

7:51:21

7:51:21

7:51:21

7:51:21

7:51:21

7:51:21

7:51:22

7:51:22

7:51:22

7:51:22

7:51:23

7:51:23

7:51:23

7:51:23

7:51:23

7:51:23

7:51:23

7:51:23

7:51:23

7:51:23

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[jbeaucaire]

Crossposted at: Excel automated random selection

Vba solution for the given problem:

Option Explicit

Sub RandomPerSecond()
Dim LR As Long, i As Long, rw As Long
Dim CurrTime As Double, MyMax As Double, MyARR As Variant

Application.ScreenUpdating = False
With ActiveSheet
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("B2:C" & LR).ClearContents
    .Range("C2:C" & LR).Formula = "=RAND()"
    ReDim MyARR(1 To LR, 1 To 3)
    MyARR = .Range("A1:C" & LR).Value
    CurrTime = MyARR(2, 1)
    For rw = 2 To LR
        If MyARR(rw, 1) = CurrTime Then
            If MyARR(rw, 3) > MyMax Then
                MyMax = MyARR(rw, 3)
                i = rw
            End If
        Else
            MyARR(i, 2) = "Keep"
            i = rw
            MyMax = 0
            CurrTime = MyARR(rw, 1)
            MyMax = MyARR(rw, 3)
        End If
        If rw = LR Then
            MyARR(i, 2) = "Keep"
            Exit For
        End If
    Next rw
    .Range("A1:C" & LR).Value = MyARR
    .Range("C2:C" & LR).ClearContents
End With
Application.ScreenUpdating = True
Beep
End Sub

 

[Joe4]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.