Can I generate a random order but with two inputted numbers?

[ned lud]

I have a list of 1-50 numbers from A1 to A50 and I have randomized them by using =rand() from b1 to b50 and then using a macro to sort by value of column B to randomize the A column.

Works perfectly.

But I want to type in two numbers in C1 and C2 between 1 and 50 and and generate a random list of the other 48 numbers without duplicating whatever I typed in cells C1 and C2.

So if I typed 8 into C1 and 22 into c2, I want the result to be a random list of the other 48 numbers without duplicating 8 and 22.

So far have tried versions of nested if statements and randbetweens etc but can't get anything to work.

If you could help, I'd really appreciate it.

 

[dreid1011]

There is likely a more elegant option, but this should get the job done:

Edit: Though, I probably should have asked, can other numbers be duplicated?

Book1
	A	B	C
1	19		15
2	16		20
3	44
4	48
5	4
6	19
7	6
8	37
9	25
10	12
11	8
12	47
13	32
14	43
15	36
16	16
17	17
18	19
19	17
20	8
21	35
22	18
23	44
24	9
25	40
26	19
27	28
28	4
29	8
30	19
31	8
32	16
33	7
34	49
35	49
36	19
37	19
38	11
39	45
40	27
41	10
42	34
43	14
44	8
45	19
46	16
47	32
48	9
49	28
50	35
Sheet2

Cell Formulas
Range	Formula
A1:A50	A1	=CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(1,MIN($C$1:$C$2)-1),RANDBETWEEN(MIN($C$1:$C$2)+1,MAX($C$1:$C$2)-1),RANDBETWEEN(MAX($C$1:$C$2)+1,50))

 

[ned lud]

Thank you.

I am a HUGE fan of inelegant solutions. I have no problem with excel working its *** off in the background behind my hidden sheets and rows and columns.

And no, none of the other numbers can be duplicates either. Sorry, I should have said that in my original post.

I tried your solution, and it works perfectly but for that one issue.

If you have any other ideas, I'd be grateful.

 

[Peter_SSs]

none of the other numbers can be duplicates either.

Try this column A formula.
The formulas in columns B & D are not required - I included those to check that numbers in column A were not duplicated and that numbers in C1:C2 did not occur in column A.

24 02 01.xlsm
	A	B	C	D
1			8	0
2	45	1	22	0
3	41	1
4	3	1
5	7	1
6	44	1
7	27	1
8	11	1
9	42	1
10	36	1
11	33	1
12	43	1
13	31	1
14	10	1
15	1	1
16	6	1
17	20	1
18	2	1
19	12	1
20	21	1
21	16	1
22	34	1
23	49	1
24	29	1
25	30	1
26	19	1
27	17	1
28	26	1
29	37	1
30	13	1
31	14	1
32	5	1
33	35	1
34	38	1
35	18	1
36	46	1
37	15	1
38	39	1
39	48	1
40	25	1
41	24	1
42	4	1
43	9	1
44	28	1
45	32	1
46	23	1
47	50	1
48	40	1
49	47	1
50
Random with exclusions

Cell Formulas
Range	Formula
A2:A49	A2	=AGGREGATE(15,6,ROW(INDEX(A:A,1):INDEX(A:A,50))/(ISNA(MATCH(ROW(INDEX(A:A,1):INDEX(A:A,50)),C$1:C$2,0))*ISNA(MATCH(ROW(INDEX(A:A,1):INDEX(A:A,50)),A$1:A1,0))),RANDBETWEEN(1,49-ROWS(A$2:A2)))
B2:B49	B2	=COUNTIF(A$2:A$49,A2)
D1:D2	D1	=COUNTIF(A$2:A$49,C1)

 

[ned lud]

Try this column A formula.
The formulas in columns B & D are not required - I included those to check that numbers in column A were not duplicated and that numbers in C1:C2 did not occur in column A.

24 02 01.xlsm
	A	B	C	D
1			8	0
2	45	1	22	0
3	41	1
4	3	1
5	7	1
6	44	1
7	27	1
8	11	1
9	42	1
10	36	1
11	33	1
12	43	1
13	31	1
14	10	1
15	1	1
16	6	1
17	20	1
18	2	1
19	12	1
20	21	1
21	16	1
22	34	1
23	49	1
24	29	1
25	30	1
26	19	1
27	17	1
28	26	1
29	37	1
30	13	1
31	14	1
32	5	1
33	35	1
34	38	1
35	18	1
36	46	1
37	15	1
38	39	1
39	48	1
40	25	1
41	24	1
42	4	1
43	9	1
44	28	1
45	32	1
46	23	1
47	50	1
48	40	1
49	47	1
50
Random with exclusions

Cell Formulas
Range	Formula
A2:A49	A2	=AGGREGATE(15,6,ROW(INDEX(A:A,1):INDEX(A:A,50))/(ISNA(MATCH(ROW(INDEX(A:A,1):INDEX(A:A,50)),C$1:C$2,0))*ISNA(MATCH(ROW(INDEX(A:A,1):INDEX(A:A,50)),A$1:A1,0))),RANDBETWEEN(1,49-ROWS(A$2:A2)))
B2:B49	B2	=COUNTIF(A$2:A$49,A2)
D1:D2	D1	=COUNTIF(A$2:A$49,C1)

Worked perfectly.

Thank you very much.

 

[Peter_SSs]

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