Random Number Generation without Repetition?

L

lukman4068

New Member
Joined
Jun 15, 2009
Messages
24
hey guys,

here's my question -

is there any way of generating random numbers within a set range in ONE cell only without having any repetition until all the numbers have been cycled through once, after which the random selection starts again.

I've tried using RANDBETWEEN but whilst this does the random number generation in once cell only, it does repeat numbers. What I would like is a similar function which does not.

So if we have 1 as the lowest number, and 75 as the highest, it should be on the 76th number generated where we have the cycle of random generation start again.

Any ideas anybody? and the numbers do have to generate in the same cell each time only.

thanks!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Hi

The solution in the link that MyExcel posted was for writing a list of unique random numbers in a range.

In this case, that you want to loop randomly through a list of numbers without repeating them until you used them all, you must use vba.

Here's how I'd do it:
- generate the array
- sort the array in a random order (you can use an array of random numbers as key)
- create a button in the worksheet
- for each click of the button use the next element of the list
- once you have used all the numbers in the list, reshuffle the array

You can use an auxilliary range to store the list of numbers or store them in vba

HTH
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
How to get random generator to pick from a list but delete the item it has picked
2
Replies
17
Views
534
Peter_SSs
Peter_SSs
D
  • Solved
Need help with random number generator
Replies
3
Views
365
Fluff
Fluff
N
  • Solved
Can I generate a random order but with two inputted numbers?
Replies
5
Views
367
Peter_SSs
Peter_SSs
B
  • Question
Random Letter and Number Generator
Replies
4
Views
730
Joe4
Joe4
G
  • Question
Random Number Generator Excluding Numbers Already in Cells
Replies
2
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,660
Messages
6,120,787
Members
448,994
Latest member
rohitsomani

Share this page

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