Random sample

[Sammilynn]

Hello,
Hopefully someone can help me out here. I am trying to find out how to right a vba code or something where I can get a certain amount of random samples from a sheet of data. There are multiple colunms with words and numbers in my data set. The data sets come with anywhere from 100-130,000 rows of data depending on the file. Based on how many rows there are will determine how many samples i will need. I would like to have a box where i could input say i need 50 samples rows and on enter i would like it to pull/copy those 50 random samples to a new worksheet. I have tried doing research but have been unsuccessful. Any help would be appreciated. Thank you!

 

[duggie33]

Without VBA, you could add a column with random numbers less than the number of rows you want to sample. The RANDBETWEEN function would work for that. Then you could filter the data showing the rows matching the random numbers.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Col 1	Col 2	Col 3	Col 4	Col 5	Col 6	Col 7	Col 8	Col 9
2	AAAAAAA	AAAAAAA	AAAAAAA	AAAAAAA	AAAAAAA	AAAAAAA	AAAAAAA	AAAAAAA	FALSE		5	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	TRUE
3	BBBBB	BBBBB	BBBBB	BBBBB	BBBBB	BBBBB	BBBBB	BBBBB	FALSE		7	DDDD	DDDD	DDDD	DDDD	DDDD	DDDD	DDDD	DDDD	FALSE
4	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	TRUE		5	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	TRUE
5	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	TRUE		2	AAAAAAA	AAAAAAA	AAAAAAA	AAAAAAA	AAAAAAA	AAAAAAA	AAAAAAA	AAAAAAA	FALSE
6	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	TRUE		4	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	CCCCCCC	TRUE
7	DDDD	DDDD	DDDD	DDDD	DDDD	DDDD	DDDD	DDDD	FALSE
8	EE	EE	EE	EE	EE	EE	EE	EE	FALSE
9	FFF	FFF	FFF	FFF	FFF	FFF	FFF	FFF	FALSE
10	G	G	G	G	G	G	G	G	TRUE
11	G	G	G	G	G	G	G	G	TRUE
12	JJJJ	JJJJ	JJJJ	JJJJ	JJJJ	JJJJ	JJJJ	JJJJ	FALSE
13	KKKKK	KKKKK	KKKKK	KKKKK	KKKKK	KKKKK	KKKKK	KKKKK	FALSE
14	LLLL	LLLL	LLLL	LLLL	LLLL	LLLL	LLLL	LLLL	FALSE
Sheet6

Cell Formulas
Range	Formula
K2:K6	K2	=RANDBETWEEN(1,13)
L2:T6	L2	=FILTER(Table4,ROW(Table4[Col 1])=$K2)
I2:I14	I2	=OR([@[Col 8]]=H1,[@[Col 8]]=H3)
Dynamic array formulas.

Hope that helps,

Doug