Help selecting random rows of data

[hnoshea]

I have an excel report that I use each month exported from our access database that containes rows of data (less than 5000) that I need to choose 100 rows of data randomly from the 5000 rows of data. Any ideas??? I have been manually choosing 100 rows at random and it takes forever - I know there must be an easier way somehow...

[Bryan Hessey]

I have an excel report that I use each month exported from our access database that containes rows of data (less than 5000) that I need to choose 100 rows of data randomly from the 5000 rows of data. Any ideas??? I have been manually choosing 100 rows at random and it takes forever - I know there must be an easier way somehow...

One-time setup: In a spare worksheet put 5000 (the number of available rows) in C1, and two numbers in D1 & E1 and in A2 the formula

=OFFSET(Sheet2!$A$1,INT(MOD(($D$1*ROW())+$E$1*ROW()*2,$C$1)),COLUMN()-1)

Formula-fill this right for the required number of columns, and then bulk-fill down for 100 rows.

Copy the result and Paste-Special = Values to where you want the data to be.

Remember to adjust cell C1 for the changing number of rows, and to put different pseudo random numbers in D1 and E1. note: Sheet2 = your data (report) sheet.


note, if you want to test the row number generated set A2 with
=INT(MOD(($D$1*ROW())+$E$1*ROW()*2,$C$1))
and formula-fill 100 rows. The row number will 'wrap' after 5000 (C1) you may wish to avoid this.

---

[hnoshea]

Thank you very much for you help!!!!