+ Reply to Thread
Results 1 to 5 of 5

Random Numbers in one column but cannot be repeated

  1. #1
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Question Random Numbers in one column but cannot be repeated

    Hi all,

    Hope I am in the right forum, if not I apologise!

    I am trying to generate up to 64 numbers in Column A. It could be any number in between, say 19. In that case, I have to get 19 numbers, from 1 to 19, in column A. So, if cell B1 (which adds up the number of opponents C1:C64) = 19, column A1:A19 would have 19 randomised numbers, with no repeats. If Cell B1 = 32, then 32 randomised no repeat numbers

    Thanks in anticipation!
    Pete
    Struggling Solutions Architect

  2. #2
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Random Numbers in one column but cannot be repeated

    Try this code for it.. (Untested Though)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    EDIT:- Worked upon some silly mistakes... ( Thanks for pointing out Holger..)
    Last edited by Vikas_Gautam; 11-01-2014 at 04:30 AM.
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Random Numbers in one column but cannot be repeated

    Hi, VFikas,
    #
    I would advise you test your codes thoropughly beofre pasting here.

    Itīs Columns("A"), and the variable r is nowshere set and thus will create a run-time error as it would be assumed to be 0. Cross-Check the code, donīt post on "thoughts" directly unless you are sure the code works fine.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    04-05-2014
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Random Numbers in one column but cannot be repeated

    Hi Pete,

    You can download the morefunc add-in. Just go to Google and search for morefunc. There is a function called MRAND in it. The Random numbers will not repeat if you use this function.

    Regards,

    Ashok

  5. #5
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: Random Numbers in one column but cannot be repeated

    Hi guys thanks for your interest

    I solved it with quite a simple remedy. In column A I had a Vlookup with conditions, so it only populated where necessary. I column B, I had a 'rand()' condition only if column A was populated, and in column C I had a ranking for values in column B.

    Sorted, and solved

    thanks anyway!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to return the column numbers of the start and finish of a merged cell? RANDOM!
    By JamesGoulding85 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-28-2013, 01:32 PM
  2. Replies: 14
    Last Post: 05-10-2013, 03:27 PM
  3. [SOLVED] VLOOKUP giving random and repeated values?!?
    By ajasons in forum Excel General
    Replies: 6
    Last Post: 05-22-2012, 11:50 AM
  4. PLS HELP: random unique-non repeated numbers.
    By Reclab in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-08-2012, 09:37 PM
  5. Generate random non-repeated range of numbers?
    By satoshi in forum Excel General
    Replies: 2
    Last Post: 06-17-2009, 05:32 AM

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