+ Reply to Thread
Results 1 to 5 of 5

I want random numbers generated without repeating

  1. #1
    Registered User
    Join Date
    11-17-2004
    Posts
    13

    I want random numbers generated without repeating

    In my worksheet in column CC in C1:C20 I want Excel to ganerate random numbers between 1 and 80 without repeating the same numbers.
    At present I have the following formula entered and it works, but after I click F9 some repeated numbers are generated.

    =INT(RAND()*(80-1)+1)

    How can I change the formula or replace it?
    Thank you in advance for your help.
    Ragards to all supporters.
    John.

  2. #2
    Registered User
    Join Date
    07-27-2006
    MS-Off Ver
    MS Office 2007
    Posts
    79
    I guess you can do it like this

    but you would need to extent column A & B if you require a bigger range of numbers (i.e. more than 1 to 80). And you would need to extend column D if you want more than 20 random numbers.

    hope this is what you want

    TL
    Attached Files Attached Files
    TL

    https://sites.google.com/site/teelim/
    My page of "not so useful" spreadsheets

  3. #3
    Registered User
    Join Date
    11-17-2004
    Posts
    13
    Quote Originally Posted by teelim
    I guess you can do it like this

    but you would need to extent column A & B if you require a bigger range of numbers (i.e. more than 1 to 80). And you would need to extend column D if you want more than 20 random numbers.

    hope this is what you want

    TL
    THANK YOU FOR YOUR REPLY.
    It's not exactly what I had in mind.
    My generated random numbers are used on the left in the rest of my worksheet.
    I hope someone can provide a simple code for this function with no repeated numbers.
    Best regards,
    John.

  4. #4
    Registered User
    Join Date
    07-27-2006
    MS-Off Ver
    MS Office 2007
    Posts
    79
    Is that actually possible?

    If anyone knows how to do it, pls share. I too am interested to know

  5. #5
    Forum Contributor
    Join Date
    11-20-2005
    Posts
    256
    Quote Originally Posted by Johncobb45
    In my worksheet in column CC in C1:C20 I want Excel to ganerate random numbers between 1 and 80 without repeating the same numbers.
    At present I have the following formula entered and it works, but after I click F9 some repeated numbers are generated.

    =INT(RAND()*(80-1)+1)

    How can I change the formula or replace it?
    Thank you in advance for your help.
    Ragards to all supporters.
    John.
    Hi John,

    I know you asked for a formula, but as you don't have any solution yet, i will throw this in.

    A Macro that, i think, does what you want, only its in a row instead of a column.
    Test it in a new workbook. You could change it to a column.

    I believe the original code is from Tom Ogilvy


    Please Login or Register  to view this content.
    Thx
    Dave
    "The game is afoot Watson"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1