Random Sampling 10% of Data

[geralde]

Hi All

I have a 290,000 rows of data and I want to randomly pick a sample of 10% of that data.

Can you please help me with a formula to do this?

Thanks

Gerald

 

[JEC]

Do you have office 365? Update your profile status pls, so we know how to help

 

[geralde]

Do you have office 365? Update your profile status pls, so we know how to help

Hi I do have office 365 on my laptop

 

[Fluff]

How about something like

=LET(Rng,Sheet1!A2:P1234,Rws,ROWS(Rng),INDEX(SORTBY(Rng,RANDARRAY(Rws)),SEQUENCE(Rws*0.1),SEQUENCE(,COLUMNS(Rng))))

 

[geralde]

How about something like

=LET(Rng,Sheet1!A2:P1234,Rws,ROWS(Rng),INDEX(SORTBY(Rng,RANDARRAY(Rws)),SEQUENCE(Rws*0.1),SEQUENCE(,COLUMNS(Rng))))

Thank you fluff for this. I am not very confident with using this formula as I might make a mess of it. For this reason, I am attaching some sample data below for you to please help demonstrate this. Column 1 is are the row of data and column 2 is where i want to pick a random sample of 10% of the column 1

Patient ID	Random10%
BT1
BT2
BT3
BT4
BT5
BT6
BT7
BT8
BT9
BT10
BT11
BT12
BT13
BT14
BT15
BT16
BT17
BT18
BT19
BT20
BT21
BT22
BT23
BT24
BT25
BT26
BT27
BT28
BT29
BT30
BT31
BT32
BT33
BT34
BT35
BT36
BT37
BT38
BT39
BT40
BT41
BT42
BT43
BT44
BT45
BT46
BT47
BT48
BT49
BT50
BT51
BT52
BT53
BT54
BT55
BT56
BT57
BT58
BT59
BT60
BT61
BT62
BT63
BT64
BT65
BT66
BT67
BT68
BT69
BT70
BT71
BT72
BT73
BT74
BT75
BT76
BT77
BT78
BT79
BT80
BT81
BT82
BT83
BT84
BT85
BT86
BT87
BT88
BT89
BT90
BT91
BT92
BT93
BT94
BT95
BT96
BT97
BT98
BT99
BT100
BT101
BT102
BT103
BT104
BT105
BT106
BT107
BT108
BT109
BT110
BT111
BT112
BT113
BT114
BT115
BT116
BT117
BT118
BT119
BT120
BT121
BT122
BT123
BT124
BT125
BT126
BT127
BT128
BT129
BT130
BT131
BT132
BT133
BT134
BT135
BT136
BT137
BT138
BT139
BT140
BT141
BT142
BT143
BT144
BT145
BT146
BT147
BT148
BT149
BT150
BT151
BT152
BT153
BT154
BT155
BT156
BT157
BT158
BT159
BT160
BT161
BT162
BT163
BT164
BT165
BT166
BT167
BT168
BT169
BT170
BT171
BT172
BT173
BT174
BT175
BT176
BT177
BT178
BT179
BT180
BT181
BT182
BT183
BT184
BT185
BT186
BT187
BT188
BT189
BT190
BT191
BT192
BT193
BT194
BT195
BT196
BT197
BT198
BT199
BT200
BT201
BT202
BT203
BT204
BT205
BT206
BT207
BT208
BT209
BT210
BT211
BT212
BT213
BT214
BT215
BT216
BT217
BT218
BT219
BT220
BT221
BT222
BT223
BT224
BT225
BT226
BT227
BT228
BT229
BT230
BT231
BT232
BT233
BT234
BT235
BT236
BT237
BT238
BT239
BT240
BT241
BT242
BT243
BT244
BT245
BT246
BT247
BT248
BT249
BT250
BT251
BT252
BT253
BT254
BT255
BT256
BT257
BT258
BT259
BT260
BT261
BT262
BT263
BT264
BT265
BT266
BT267
BT268
BT269
BT270
BT271
BT272
BT273
BT274
BT275
BT276
BT277
BT278
BT279

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B
1	Patient ID	Random10%
2	BT1	BT196
3	BT2	BT266
4	BT3	BT108
5	BT4	BT35
6	BT5	BT19
7	BT6	BT41
8	BT7	BT65
9	BT8	BT151
10	BT9	BT273
11	BT10	BT72
12	BT11	BT57
13	BT12	BT225
14	BT13	BT50
15	BT14	BT137
16	BT15	BT264
17	BT16	BT262
18	BT17	BT173
19	BT18	BT129
20	BT19	BT142
21	BT20	BT246
22	BT21	BT263
23	BT22	BT258
24	BT23	BT201
25	BT24	BT161
26	BT25	BT37
27	BT26	BT236
28	BT27	BT272
29	BT28
30	BT29
31	BT30
32	BT31
33	BT32
34	BT33
35	BT34
36	BT35
37	BT36
38	BT37
39	BT38
40	BT39
41	BT40
42	BT41
43	BT42
44	BT43
45	BT44
46	BT45
47	BT46
48	BT47
49	BT48
50	BT49
51	BT50
52	BT51
53	BT52
54	BT53
55	BT54
56	BT55
57	BT56
58	BT57
59	BT58
60	BT59
61	BT60
62	BT61
63	BT62
64	BT63
65	BT64
66	BT65
67	BT66
68	BT67
69	BT68
70	BT69
71	BT70
72	BT71
73	BT72
74	BT73
75	BT74
76	BT75
77	BT76
78	BT77
79	BT78
80	BT79
81	BT80
82	BT81
83	BT82
84	BT83
85	BT84
86	BT85
87	BT86
88	BT87
89	BT88
90	BT89
91	BT90
92	BT91
93	BT92
94	BT93
95	BT94
96	BT95
97	BT96
98	BT97
99	BT98
100	BT99
101	BT100
102	BT101
103	BT102
104	BT103
105	BT104
106	BT105
107	BT106
108	BT107
109	BT108
110	BT109
111	BT110
112	BT111
113	BT112
114	BT113
115	BT114
116	BT115
117	BT116
118	BT117
119	BT118
120	BT119
121	BT120
122	BT121
123	BT122
124	BT123
125	BT124
126	BT125
127	BT126
128	BT127
129	BT128
130	BT129
131	BT130
132	BT131
133	BT132
134	BT133
135	BT134
136	BT135
137	BT136
138	BT137
139	BT138
140	BT139
141	BT140
142	BT141
143	BT142
144	BT143
145	BT144
146	BT145
147	BT146
148	BT147
149	BT148
150	BT149
151	BT150
152	BT151
153	BT152
154	BT153
155	BT154
156	BT155
157	BT156
158	BT157
159	BT158
160	BT159
161	BT160
162	BT161
163	BT162
164	BT163
165	BT164
166	BT165
167	BT166
168	BT167
169	BT168
170	BT169
171	BT170
172	BT171
173	BT172
174	BT173
175	BT174
176	BT175
177	BT176
178	BT177
179	BT178
180	BT179
181	BT180
182	BT181
183	BT182
184	BT183
185	BT184
186	BT185
187	BT186
188	BT187
189	BT188
190	BT189
191	BT190
192	BT191
193	BT192
194	BT193
195	BT194
196	BT195
197	BT196
198	BT197
199	BT198
200	BT199
201	BT200
202	BT201
203	BT202
204	BT203
205	BT204
206	BT205
207	BT206
208	BT207
209	BT208
210	BT209
211	BT210
212	BT211
213	BT212
214	BT213
215	BT214
216	BT215
217	BT216
218	BT217
219	BT218
220	BT219
221	BT220
222	BT221
223	BT222
224	BT223
225	BT224
226	BT225
227	BT226
228	BT227
229	BT228
230	BT229
231	BT230
232	BT231
233	BT232
234	BT233
235	BT234
236	BT235
237	BT236
238	BT237
239	BT238
240	BT239
241	BT240
242	BT241
243	BT242
244	BT243
245	BT244
246	BT245
247	BT246
248	BT247
249	BT248
250	BT249
251	BT250
252	BT251
253	BT252
254	BT253
255	BT254
256	BT255
257	BT256
258	BT257
259	BT258
260	BT259
261	BT260
262	BT261
263	BT262
264	BT263
265	BT264
266	BT265
267	BT266
268	BT267
269	BT268
270	BT269
271	BT270
272	BT271
273	BT272
274	BT273
275	BT274
276	BT275
277	BT276
278	BT277
279	BT278
280	BT279
Master

Cell Formulas
Range	Formula
B2:B28	B2	=LET(Rng,FILTER(A2:A1000,A2:A1000<>""),Rws,ROWS(Rng),INDEX(SORTBY(Rng,RANDARRAY(Rws)),SEQUENCE(Rws*0.1),SEQUENCE(,COLUMNS(Rng))))
Dynamic array formulas.

 

[JEC]

=INDEX(SORTBY(FILTER(A2:A300000;A2:A300000<>"");RANDARRAY(COUNTA(A2:A300000)));SEQUENCE(COUNTA(A2:A300000)*0,1))

 

[geralde]

=INDEX(SORTBY(FILTER(A2:A300000;A2:A300000<>"");RANDARRAY(COUNTA(A2:A300000)));SEQUENCE(COUNTA(A2:A300000)*0,1))

thak you so much for this. Much appreciated

 

[geralde]

Ok, how about

+Fluff 1.xlsm
	A	B
1	Patient ID	Random10%
2	BT1	BT196
3	BT2	BT266
4	BT3	BT108
5	BT4	BT35
6	BT5	BT19
7	BT6	BT41
8	BT7	BT65
9	BT8	BT151
10	BT9	BT273
11	BT10	BT72
12	BT11	BT57
13	BT12	BT225
14	BT13	BT50
15	BT14	BT137
16	BT15	BT264
17	BT16	BT262
18	BT17	BT173
19	BT18	BT129
20	BT19	BT142
21	BT20	BT246
22	BT21	BT263
23	BT22	BT258
24	BT23	BT201
25	BT24	BT161
26	BT25	BT37
27	BT26	BT236
28	BT27	BT272
29	BT28
30	BT29
31	BT30
32	BT31
33	BT32
34	BT33
35	BT34
36	BT35
37	BT36
38	BT37
39	BT38
40	BT39
41	BT40
42	BT41
43	BT42
44	BT43
45	BT44
46	BT45
47	BT46
48	BT47
49	BT48
50	BT49
51	BT50
52	BT51
53	BT52
54	BT53
55	BT54
56	BT55
57	BT56
58	BT57
59	BT58
60	BT59
61	BT60
62	BT61
63	BT62
64	BT63
65	BT64
66	BT65
67	BT66
68	BT67
69	BT68
70	BT69
71	BT70
72	BT71
73	BT72
74	BT73
75	BT74
76	BT75
77	BT76
78	BT77
79	BT78
80	BT79
81	BT80
82	BT81
83	BT82
84	BT83
85	BT84
86	BT85
87	BT86
88	BT87
89	BT88
90	BT89
91	BT90
92	BT91
93	BT92
94	BT93
95	BT94
96	BT95
97	BT96
98	BT97
99	BT98
100	BT99
101	BT100
102	BT101
103	BT102
104	BT103
105	BT104
106	BT105
107	BT106
108	BT107
109	BT108
110	BT109
111	BT110
112	BT111
113	BT112
114	BT113
115	BT114
116	BT115
117	BT116
118	BT117
119	BT118
120	BT119
121	BT120
122	BT121
123	BT122
124	BT123
125	BT124
126	BT125
127	BT126
128	BT127
129	BT128
130	BT129
131	BT130
132	BT131
133	BT132
134	BT133
135	BT134
136	BT135
137	BT136
138	BT137
139	BT138
140	BT139
141	BT140
142	BT141
143	BT142
144	BT143
145	BT144
146	BT145
147	BT146
148	BT147
149	BT148
150	BT149
151	BT150
152	BT151
153	BT152
154	BT153
155	BT154
156	BT155
157	BT156
158	BT157
159	BT158
160	BT159
161	BT160
162	BT161
163	BT162
164	BT163
165	BT164
166	BT165
167	BT166
168	BT167
169	BT168
170	BT169
171	BT170
172	BT171
173	BT172
174	BT173
175	BT174
176	BT175
177	BT176
178	BT177
179	BT178
180	BT179
181	BT180
182	BT181
183	BT182
184	BT183
185	BT184
186	BT185
187	BT186
188	BT187
189	BT188
190	BT189
191	BT190
192	BT191
193	BT192
194	BT193
195	BT194
196	BT195
197	BT196
198	BT197
199	BT198
200	BT199
201	BT200
202	BT201
203	BT202
204	BT203
205	BT204
206	BT205
207	BT206
208	BT207
209	BT208
210	BT209
211	BT210
212	BT211
213	BT212
214	BT213
215	BT214
216	BT215
217	BT216
218	BT217
219	BT218
220	BT219
221	BT220
222	BT221
223	BT222
224	BT223
225	BT224
226	BT225
227	BT226
228	BT227
229	BT228
230	BT229
231	BT230
232	BT231
233	BT232
234	BT233
235	BT234
236	BT235
237	BT236
238	BT237
239	BT238
240	BT239
241	BT240
242	BT241
243	BT242
244	BT243
245	BT244
246	BT245
247	BT246
248	BT247
249	BT248
250	BT249
251	BT250
252	BT251
253	BT252
254	BT253
255	BT254
256	BT255
257	BT256
258	BT257
259	BT258
260	BT259
261	BT260
262	BT261
263	BT262
264	BT263
265	BT264
266	BT265
267	BT266
268	BT267
269	BT268
270	BT269
271	BT270
272	BT271
273	BT272
274	BT273
275	BT274
276	BT275
277	BT276
278	BT277
279	BT278
280	BT279
Master

Cell Formulas
Range	Formula
B2:B28	B2	=LET(Rng,FILTER(A2:A1000,A2:A1000<>""),Rws,ROWS(Rng),INDEX(SORTBY(Rng,RANDARRAY(Rws)),SEQUENCE(Rws*0.1),SEQUENCE(,COLUMNS(Rng))))
Dynamic array formulas.

thank you so much for this. much appreciated

 

[Fluff]

Glad we could help & thanks for the feedback.