Select Random Rows of Data

**ThxInAdvance** · 12-10-2014, 02:25 PM

I am using excel 2013. My sheet has 32 columns and 139 rows (we will be adding rows later). We need to randomly select 10 rows of data. This is something that we will be doing weekly.

**Richard Buttrey** · 12-10-2014, 02:42 PM

Hi,

I'm attaching an example of one approach you could use. Columns A&B are two helper columns. The formula are currently set to pick 4 rows from a range of 20 so you just need to adapt the range A1:A20 and change to A1:A139 (or whatever the range is), better still of you're going to be adding rows create a dynamic range name and use that in the column B formula so that it will automatically adjust your 139 rows when necessary. And change the <=4 to <=10

This will paint the rows yellow. If you actually want to select the rows then you could have a simple macro that selected the column B cells that contain the value Y.

**ThxInAdvance** · 12-10-2014, 03:36 PM

This was helpful, but I'm having a little trouble. I used your formula in column B, but changed the range to A2:A140. My data has headers. I'm getting a #NUM error in cell B140.

I've also been asked to have the random data automatically go into a new sheet in the workbook.

**ThxInAdvance** · 12-10-2014, 03:38 PM

The new page should have all 32 columns of the 10 randomly selected rows.

**Richard Buttrey** · 12-10-2014, 03:51 PM

Hi,

Rather than guessing on what your data actually looks like it would be preferable if you upload the actual workbook so that we have something concrete with which to work.

**ThxInAdvance** · 12-10-2014, 04:22 PM

I've removed personal data. My original workbook has formulas in some of the columns to calculate the number of days between dates. This one only contains values only.