Excel VBA Random User data select and display

imgaur7

New Member
Joined
Dec 25, 2017
Messages
31
I am working on some excel data where in need to randomly select 5 (or 10) equal number of data from different user and display in a different sheet. As it is big sheet it becomes a time taking process, and for that I am looking to get a VBA which may go ahead and select random data and display.

Eg: (need to randomly select 3 data from each user A0123 and B0123)
UserData1Data2Data3Data4
A012312350112457547896546231
A01234568453215646554454457545565
A0123901340543004731034299414544899
A01231345835764444811524054321544233
A01231790330985884892013809228543567
A012322348261107324972503564135542901
A012326793213xbcs2993319042542235
A012331238165150205133483073949541569
B012335683117171645213972828856540903
B01234012806919308529ytytt63540237
B012344573021fhghg4952338670539571
B012349017973235965455442093577538905
B0123534629252574055359318484fgfgfg
B0123sadsd278845616421603391537573
B012362352829300285696911358298536907
B0123667977813217257774011131105536241

<tbody>
</tbody>

Kindly suggest as how to go ahead with it. Thanks to all in advance.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
If the table is in A1 then to randomly choose data for A0123 then for example...

Code:
Dim rng As Range
Dim randomRow As Long, randomColumn As Long

Randomize
randomRow = CLng((8 - 2 + 1) * Rnd + 2)

Randomize
randomColumn = CLng((5 - 2 + 1) * Rnd + 2)

Set rng = Cells(randomRow, randomColumn)
https://www.techonthenet.com/excel/formulas/rnd.php (use Long for row numbers)

that would choose a random cell from rows 2-8 and columns B-E

do that 5, 10, 3 times... not sure, your explanation wasn't really clear

you can use collections to keep track of selections and to retry if you get repeats, unless repeats dont matter. Also you can search first to see what rows A0123 is on if it can be different. There are functions to find text and then get the row from the range or use a loop and check each cell yourself.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,475
Messages
5,529,062
Members
409,849
Latest member
J7House1984
Top