Data Sampling, random but consecutive

[kirkley08]

Hi all, so I have this big data that I need to check by taking sample randomly, but I want the sample data to be somehow consecutive, like consecutive 4 data from any number/row I choose.

For example I have 200 datas and I need to take and check 20 samples out of them, I want the samples to be present in the next column like data 3 to 6 (4 samples), data 20 to 24 (4 samples), 86 to 89, 111 to 115 and lastly 169 to 172.

So the number 3, 20, 86, 111 and 169 is chosen randomly, but will be the starting number of the next 3 samples, if that makes sense.

Thank you!

 

[pitchoute]

You need starting positions between 1 and 197 (since starting at 197 will include data points up to 200).
In cells B1 to B5, enter the following formula to generate random integers:

=RANDBETWEEN(1,197)

This will give you 5 random starting positions.

In cell C1, extract the first data point using:

=INDEX($A$1:$A$200, $B1)

In cell D1:

=INDEX($A$1:$A$200, $B1+1)

In cell E1:

=INDEX($A$1:$A$200, $B1+2)

In cell F1:

=INDEX($A$1:$A$200, $B1+3)

 

[Bond00]

To take the answer above a step further in case you don't fully follow it. This is how you can put it into practice for what you need.

00 test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Data		Number	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
2	5AB102		RNG #	2	17	23	32	46	53	65	70	82	92	102	111	126	137	142	153	164	177	184	197
3	5AB103		sample 1	5AB102	5AB117	5AB123	5AB132	5AB146	5AB153	5AB165	5AB170	5AB182	5AB192	5AB202	5AB211	5AB226	5AB237	5AB242	5AB253	5AB264	5AB277	5AB284	5AB297
4	5AB104		sample 2	5AB103	5AB118	5AB124	5AB133	5AB147	5AB154	5AB166	5AB171	5AB183	5AB193	5AB203	5AB212	5AB227	5AB238	5AB243	5AB254	5AB265	5AB278	5AB285	5AB298
5	5AB105		sample 3	5AB104	5AB119	5AB125	5AB134	5AB148	5AB155	5AB167	5AB172	5AB184	5AB194	5AB204	5AB213	5AB228	5AB239	5AB244	5AB255	5AB266	5AB279	5AB286	5AB299
6	5AB106		sample 4	5AB105	5AB120	5AB126	5AB135	5AB149	5AB156	5AB168	5AB173	5AB185	5AB195	5AB205	5AB214	5AB229	5AB240	5AB245	5AB256	5AB267	5AB280	5AB287	5AB300
7	5AB107
8	5AB108
9	5AB109
10	5AB110
11	5AB111
12	5AB112
13	5AB113
14	5AB114
15	5AB115
16	5AB116
17	5AB117
18	5AB118
19	5AB119
20	5AB120
Sheet1

Cell Formulas
Range	Formula
D2	D2	=RANDBETWEEN(2,D1*10-3)
E2:W2	E2	=RANDBETWEEN(D1*10,E1*10-3)
D3:W3	D3	=INDEX($A$1:$A$200, D2)
D4:W4	D4	=INDEX($A$1:$A$200, D2+1)
D5:W5	D5	=INDEX($A$1:$A$200, D2+2)
D6:W6	D6	=INDEX($A$1:$A$200, D2+3)

 

[kirkley08]

Thank you for both of you!
I tried it just now and it works!

 

[Peter_SSs]

I tried it just now and it works!

That depends on how particular you are and how random you want the results to be.

The post 2 suggestion can lead to data being repeated. Here is an example that came up for me using that suggestion.

kirkley08.xlsm
	A	B	C	D	E	F
1		64	5ABC185	5ABC186	5ABC187	5ABC188
2	5ABC123	70	5ABC191	5ABC192	5ABC193	5ABC194
3	5ABC124	179	5ABC300	5ABC301	5ABC302	5ABC303
4	5ABC125	69	5ABC190	5ABC191	5ABC192	5ABC193
5	5ABC126	58	5ABC179	5ABC180	5ABC181	5ABC182
6	5ABC127
7	5ABC128
8	5ABC129
9	5ABC130
10	5ABC131
11	5ABC132
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=RANDBETWEEN(1,197)
C1:C5	C1	=INDEX($A$1:$A$200, $B1)
D1:D5	D1	=INDEX($A$1:$A$200, $B1+1)
E1:E5	E1	=INDEX($A$1:$A$200, $B1+2)
F1:F5	F1	=INDEX($A$1:$A$200, $B1+3)

Unless I am misunderstanding how it is supposed to be interpreted, the post 3 suggestion can also contain repeats. Here is an example that came up for me using that suggestion.

kirkley08.xlsm
	A	B	C	D	E	F	G
1	Data		Number	1	2	3	4
2	5AB102		RNG #	3	17	20	36
3	5AB103		sample 1	5AB103	5AB117	5AB120	5AB136
4	5AB104		sample 2	5AB104	5AB118	5AB121	5AB137
5	5AB105		sample 3	5AB105	5AB119	5AB122	5AB138
6	5AB106		sample 4	5AB106	5AB120	5AB123	5AB139
7	5AB107
8	5AB108
9	5AB109
10	5AB110
11	5AB111
Sheet3

Cell Formulas
Range	Formula
D2	D2	=RANDBETWEEN(2,D1*10-3)
E2:G2	E2	=RANDBETWEEN(D1*10,E1*10-3)
D3:G3	D3	=INDEX($A$1:$A$200, D2)
D4:G4	D4	=INDEX($A$1:$A$200,D2+1)
D5:G5	D5	=INDEX($A$1:$A$200,D2+2)
D6:G6	D6	=INDEX($A$1:$A$200,D2+3)

Also, the post 3 suggestion will not produce truly random samples. For example, if truly random, it would be possible that at some stage the data from, say, A2:A5 could be in the sample and the data from, say, A7:A10 could also be part of the random sample. That combination would never be possible with the post 3 suggestion.

 

[kirkley08]

Ahh yeah, now that you mention it I found that bug too. I tried adding RANK formula in the INDEX formula from post 2 hoping that I won't get duplicate results, well there are still some duplicates but not as frequent as previous one (or maybe my understanding of RANK formula is wrong?).