Generate permutations of r length from list of n length

[healmo]

I have a list of 70 pieces of equipment in one column. Need to generate random picks of 21 of those equipment. Ideally generate all possible lists of 21 pieces of equipment out of the list of 70. Don’t know where to start in Excel. I’m not a programmer, I just try to use formulas in Excel.

 

[saboh12617]

Hello,

To generate one random list of 21 picks in a 70 set is okay. However generate all of them… Just write =COMBIN(70, 21) in a cell to get an idea of how much they are.

 

[healmo]

Hello,

To generate one random list of 21 picks in a 70 set is okay. However generate all of them… Just write =COMBIN(70, 21) in a cell to get an idea of how much they are.

You’re right, too many. I could start generating a couple random lists of 21 picks from a 70 set. Do you know how I could do that?

 

[Peter_SSs]

Welcome to the MrExcel board!

Here is a smaller example that you could scale up.

25 02 13.xlsm
	A	B
1
2	Item 1	Item 2
3	Item 2	Item 18
4	Item 3	Item 6
5	Item 4	Item 17
6	Item 5	Item 16
7	Item 6	Item 3
8	Item 7
9	Item 8
10	Item 9
11	Item 10
12	Item 11
13	Item 12
14	Item 13
15	Item 14
16	Item 15
17	Item 16
18	Item 17
19	Item 18
20	Item 19
21	Item 20
22
Random list

Cell Formulas
Range	Formula
B2:B7	B2	=TAKE(SORTBY(A2:A21,RANDARRAY(ROWS(A2:A21))),6)
Dynamic array formulas.

 

[healmo]

Peter_SSs thanks a lot! This worked perfect!!!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.