Generate Random Number Wihout Repeat In One Cell

[Kumara_faith]

Hi,

I am creating a sheet in excel for my company lucky draw. I have 500 participants and I need to create a formula in a single cell that would randomly generate numbers between 001 to 500 without repetitions in one single cell when the guest of honor hits F9. I tried RAND and then RANDBETWEEN but it does generate duplicates.

I have looked through the other post but most are VBA and does not generate in one single cell.

Appreciate all the help I could get.

I have a cross post here:

http://www.mrexcel.com/forum/excel-q...-one-cell.html

[Kumara_faith]

Hi,

Just to add that the numbers needs to be generated in one cell and one by one for 50 numbers as we have 50 lucky draws to be given away.

[Tony Valko]

This can not be done using a single formula in a single cell.

Why do you want all the numbers in a single cell?

With the RAND and RANDBETWEEN functions, each time you press function key F9 they will generate new numbers.

[ncmay]

The attached workbook will display 50 random non repeating numbers from 1 to 500 in 1 cell.
Columns A:D can be hidden.

[Tony Valko]

Now I have to edit my previous reply!

[Kumara_faith]

Hi,

Thank you for your time and patience. My apologies as I might not have been clear in my post. We will set up the excel screen and as the VIP hits F9, one by one random numbers from 001-500 will populate in a single cell ( not all 50 random numbers at once) and the lucky draw prizes will be given for 50 people. Based on the spreadsheet, can I use cell C2 for this purpose?

[Tony Valko]

You'll need a VBA procedure to do it the way you want.

I'm not much of a programmer so someone else will need to help you.

You should post this question in the Excel Programming / VBA / Macros forum. Contact any moderator or administrator and ask them to move this thread into that forum.

Good luck!

[Kumara_faith]

Hi,

Ok , I will do that.

Just to add: Is there a way we can assign the macro to a button and when we click the macro once, the numbers run many time and then come to a stop for the winning number just like a slot machine? We can then do this for 50 times for all 50 winners.

[Tony Valko]

If you had Excel 2013, it already does that!

It sounds feasible but I don't know for sure as I'm not much of a programmer.

[no.19]

Thanks all, I have to use lucky draw random every month, this may save my time a lot.