Selecting random rows based on criteria in other columns

[Insert Name]

I would like to select cells from a specific column, and produce the rows.
So I might want to pick a random ID, but filter/choose by combinations of event type. I can delete rows based on event, but it would be nice to have something that can do it all together. In the data below the event types can occur more than once. I have posted it just to give a visual example.

[Glenn Kennedy]

I don't follow what you want. Can you be a bit more specific? Also - is it a VBA solution that you definitely want?

[Insert Name]

No, I just thought that VBA would be the best solution.

I want a formula that will randomly pick a row. However, sometimes I might want to subset my data.

So it might look like: if event 1 = DY85, then pick a random row with this constraint. So the output might be:
Name ID Event1 Event2 Value Value2
J 40 DY85 OZPV 0.599 -7

Or I might want to subset by adding constraints based on event1 and event2.
Does this help?

[Glenn Kennedy]

Yea, you probably need at least some VBA. The formulas sued here count the number of rows that contain the search term (J1) - let's call it n. RANDBETWEEN then randomly selects an integer between 1 and n and returns the corresponding mtching row.

However...


Every time it recalculates (data re-entry, hit F9, etc) a new random number is generated and a new result appears. Someone out there should be able to add on a bit of VBA to give you a one-off (non-updating) randomly selected number in O1. In which case, problem solved??

[Insert Name]

Thank you!