Random number what's left

[innmentor]

Hi newby here so please simplest answer as I have a very small brain and rely on available functions etc and haven't "lifted the bonnet" as it were to delve into the codes.

I have put together a simple list of people's details for a 100 club to raise funds, I am using the excel form to input data easily. Each person can have up to 4 no's allocated against their name and they state on the entry form how many. These are then allocated from the initial hundred on offer. Rather than allocating by hand we would like a random number generated to be allocated to dispel any " my favourite" number requests. The problem is that generating a random number is done between specified limits which means that an allocated number cam be generated when allocated.Is there a way of automatically removing any allocated number from those available for selection from the grid. This would also help for when people drop out and their numbers are removed they appear back in the grid again for re-allocation.
I hope I have explained what I need and add the graphic to illustrate it hopefully better

 

[DRSteele]

Is this what your after? You wish to generate random numbers without repeats, I gather.

 

[innmentor]

Is this what your after? You wish to generate random numbers without repeats, I gather.

Hi thanks for your reply. Have tried to install the xl12bb tool as requested get to the add ins availablemenu and only Analysis toolpack, Analysis toolpak VBA.euro currency and solver add in are shown. Clicking the browse button just opens the browser not the list of files available. Where is the xl12bb tool stored.
Please excuse my ignorance oh enlightened one

 

[DRSteele]

Were you able to follow the link in Post #2 that discusses random number generating?

As for the add-in, this page should is helpful.

 

[innmentor]

Were you able to follow the link in Post #2 that discusses random number generating?

As for the add-in, this page should is helpful.

Hi yes thanks have gone through the pointers in the link and the new array formulas look very interesting but still cannot fathom out the basic problem of removing allocated numbers from the 1-100 matrix (however laid out)from which the remainder of randomly numbers can suggest the next number to be allocated.I am starting to think this may be undo able in excel and we will just have to resort to a printed grid to cross off numbers as they are allocated to people rather than automating it in the spreadsheet.