Random number what's left

innmentor

New Member
Joined
Jan 27, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • 100 club question.jpg
    100 club question.jpg
    130.3 KB · Views: 19

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,309
Office Version
  1. 365
Platform
  1. Windows
Is this what your after? You wish to generate random numbers without repeats, I gather.
 

innmentor

New Member
Joined
Jan 27, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:rolleyes:
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,309
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 27, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,084
Messages
5,545,867
Members
410,711
Latest member
Josh324
Top