Using Rand Function to Select 50 Rows

Hi All,

Apologies first as I am new to Excel and have a fairly limited understanding. I have been tasked to try and find a way to select 50 random tickets from a weekly report containing over 200+ tickets. So far I have been able to do a work around by copying out the Ticket ID(Column A) assigning it a value and hiding the column then using: =INDEX($A:$A, RANK(N32,$N:$N), 1) In this case I've copied my ID field into a separate sheet to column A, put the hidden values in Column N then run the above in Column B.


This kind of works but I'm then having to take the random ticket ID's that have been generated and do a manual search for the data in the original sheet to copy and paste into a new sheet with all the details.

So I guess the short version is - I'm trying to grab 50 random rows from a sheet filled with 100+ and output it to it's own sheet, is there a simple way to do this?

Below is a test example of the type of data I'm dealing with, any help would be massively appreciated as I'm just running in circles at this point

test 2.PNG

One of the problems with RAND is that it is volatile. Change anything, anywhere and the values change... and then so will your results. An alternative is needed!!

Will there be differences in the number of rows on each weekly sheet... 210 one week, 184 the next, etc, etc??

No the variable numbers are PERFECT. It's what I was hoping to hear!!

I always use this to generate UNIQUE non-volatile pseudo-random numbers:

=MOD(ROW()/PI(),1)

It has one disadvantage. If it starts on row 2, it always generates the same random number. So, in your case, to introduce a bit of variability, I modified it to:

=MOD((ROW()+COUNTA(A:A))/PI(),1)

So, the numbers are fully randomised. The first random number will vary depending on the number of non-blank rows in your sample. Use that as a helper column (Shhet 1, L2, copied down).

Then this in sheet 2,A2, copied across and down (in this case 3 rows... in your real data by 50 rows)

=INDEX(Sheet1!A$2:A$7,MATCH(1,--(Sheet1!$L$2:$L$7=SMALL(Sheet1!$L$2:$L$7,ROWS(B$2:B2))),0))

Never. The helper sequence is entirely unique. Tested on 1,048,576 rows!!

The formulae are all set to go to row 7. Change the 7s to a sensible number.... 500 or 1000.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

Please post a file showing the issue.

Hi,

Why not add a helper column with a RAND() function to the data then sort the data including the helper column, then simply copy the first 50 rows of data, either manually or a simple macro like say.

Range("A1:A50").EntireRow.Copy mysheetname.Range("A1")

If you want to preserve the original data order other than by using the date column add an extra helper column that contains a sequential number the after you've extracted your 50 rows resort back on the original order column