Need Random number generator formula

[neil_c]

Hi, I want to generate 20 random numbers between 1-80, but my formula always bringing duplicates also. So is there any way to generate 20 sorted numbers in ascending order without duplicates and I want to save those numbers at runtime. it will be easy if the formula can generate numbers for 100 rows.

 

[Phuoc]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Random	1	2	8	15	20	21	24	32	33	36	44	48	51	52	59	61	66	69	76	78
2		3	8	10	15	16	18	27	30	36	39	44	48	52	53	57	62	63	64	65	67
3		3	7	10	13	16	24	26	29	31	38	44	46	48	52	58	61	62	64	70	79
4		4	6	10	11	14	24	26	32	34	40	43	47	51	55	58	61	65	69	74	76
5		2	5	10	14	17	20	23	32	36	37	42	46	52	53	59	63	65	71	74	78
6		2	8	9	14	19	21	28	32	34	38	39	40	44	56	60	61	62	69	76	79
7		4	5	10	13	14	17	27	29	35	36	44	45	49	56	59	63	65	66	73	74
8		4	6	9	11	20	24	27	29	31	34	37	40	46	55	59	63	65	66	70	72
9		1	8	11	12	13	22	27	29	34	40	44	46	51	56	59	63	67	68	74	75
10		3	5	10	11	12	19	21	24	25	40	43	47	49	50	52	60	62	64	76	78
11		3	6	12	14	17	21	22	24	34	40	44	47	50	56	60	62	66	67	72	75
12		1	5	10	12	16	19	24	29	30	35	38	46	52	55	59	64	65	70	76	79
13		2	8	9	13	19	21	28	31	32	40	41	46	52	56	60	62	67	68	75	76
14		4	8	9	16	18	23	26	28	35	38	42	47	52	55	56	57	61	71	73	74
15		1	8	12	15	19	22	26	27	32	33	43	45	52	55	60	61	66	71	75	77
16		2	7	9	12	15	18	21	24	31	38	43	46	51	52	55	60	64	67	73	75
17		1	2	11	14	18	24	27	31	32	36	44	46	47	55	58	61	63	71	73	80
18		4	6	12	15	18	24	25	26	35	38	42	47	52	55	59	61	63	64	69	77
19		1	5	9	16	19	23	26	31	32	35	43	45	47	49	50	60	61	64	66	79
20		1	6	9	12	14	21	24	28	30	33	39	40	46	54	59	63	64	70	74	75
21		4	6	12	13	14	20	27	32	33	36	43	47	49	51	59	62	68	69	70	73
Sheet1

Cell Formulas
Range	Formula
B1:U21	B1	=RANDBETWEEN(MAX($A1:A1)+1,COLUMNS($A1:A1)*4)

 

[neil_c]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Random	1	2	8	15	20	21	24	32	33	36	44	48	51	52	59	61	66	69	76	78
2		3	8	10	15	16	18	27	30	36	39	44	48	52	53	57	62	63	64	65	67
3		3	7	10	13	16	24	26	29	31	38	44	46	48	52	58	61	62	64	70	79
4		4	6	10	11	14	24	26	32	34	40	43	47	51	55	58	61	65	69	74	76
5		2	5	10	14	17	20	23	32	36	37	42	46	52	53	59	63	65	71	74	78
6		2	8	9	14	19	21	28	32	34	38	39	40	44	56	60	61	62	69	76	79
7		4	5	10	13	14	17	27	29	35	36	44	45	49	56	59	63	65	66	73	74
8		4	6	9	11	20	24	27	29	31	34	37	40	46	55	59	63	65	66	70	72
9		1	8	11	12	13	22	27	29	34	40	44	46	51	56	59	63	67	68	74	75
10		3	5	10	11	12	19	21	24	25	40	43	47	49	50	52	60	62	64	76	78
11		3	6	12	14	17	21	22	24	34	40	44	47	50	56	60	62	66	67	72	75
12		1	5	10	12	16	19	24	29	30	35	38	46	52	55	59	64	65	70	76	79
13		2	8	9	13	19	21	28	31	32	40	41	46	52	56	60	62	67	68	75	76
14		4	8	9	16	18	23	26	28	35	38	42	47	52	55	56	57	61	71	73	74
15		1	8	12	15	19	22	26	27	32	33	43	45	52	55	60	61	66	71	75	77
16		2	7	9	12	15	18	21	24	31	38	43	46	51	52	55	60	64	67	73	75
17		1	2	11	14	18	24	27	31	32	36	44	46	47	55	58	61	63	71	73	80
18		4	6	12	15	18	24	25	26	35	38	42	47	52	55	59	61	63	64	69	77
19		1	5	9	16	19	23	26	31	32	35	43	45	47	49	50	60	61	64	66	79
20		1	6	9	12	14	21	24	28	30	33	39	40	46	54	59	63	64	70	74	75
21		4	6	12	13	14	20	27	32	33	36	43	47	49	51	59	62	68	69	70	73
Sheet1

Cell Formulas
Range	Formula
B1:U21	B1	=RANDBETWEEN(MAX($A1:A1)+1,COLUMNS($A1:A1)*4)

Great solution but it keeps changing the values when I insert a new row, which it shouldn't change.
is there a way to lock the values at runtime? i mean i want to save those random numbers without any change.

 

[Phuoc]

You can Copy it and Paste Value

 

[neil_c]

You can Copy it and Paste Value

YES, this is what I thought to do. just a quick question: can I add the number of lines command to the formula so that I don't have to copy and drag down. so it can automatically generate numbers up to a given specific line number.

 

[Phuoc]

VBA can do that.