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

ned lud

New Member
Joined
Jan 30, 2024
Messages
15
Office Version
  1. 2019
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
ABC
11915
21620
344
448
54
619
76
837
925
1012
118
1247
1332
1443
1536
1616
1717
1819
1917
208
2135
2218
2344
249
2540
2619
2728
284
298
3019
318
3216
337
3449
3549
3619
3719
3811
3945
4027
4110
4234
4314
448
4519
4616
4732
489
4928
5035
Sheet2
Cell Formulas
RangeFormula
A1:A50A1=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))
 
Upvote 0
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.
 
Upvote 0
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
ABCD
180
2451220
3411
431
571
6441
7271
8111
9421
10361
11331
12431
13311
14101
1511
1661
17201
1821
19121
20211
21161
22341
23491
24291
25301
26191
27171
28261
29371
30131
31141
3251
33351
34381
35181
36461
37151
38391
39481
40251
41241
4241
4391
44281
45321
46231
47501
48401
49471
50
Random with exclusions
Cell Formulas
RangeFormula
A2:A49A2=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:B49B2=COUNTIF(A$2:A$49,A2)
D1:D2D1=COUNTIF(A$2:A$49,C1)
 
Upvote 0
Solution
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
ABCD
180
2451220
3411
431
571
6441
7271
8111
9421
10361
11331
12431
13311
14101
1511
1661
17201
1821
19121
20211
21161
22341
23491
24291
25301
26191
27171
28261
29371
30131
31141
3251
33351
34381
35181
36461
37151
38391
39481
40251
41241
4241
4391
44281
45321
46231
47501
48401
49471
50
Random with exclusions
Cell Formulas
RangeFormula
A2:A49A2=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:B49B2=COUNTIF(A$2:A$49,A2)
D1:D2D1=COUNTIF(A$2:A$49,C1)
Worked perfectly.

Thank you very much.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,224,567
Messages
6,179,571
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top