Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    I have a spreadsheet that generates random names, work OK but generates duplicates names - see sample attached. Is there a way to generate random unique names only - no duplicates
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would use a random number in the A column and use the large function (or small function) to grab the largest or smallest values from the random numbers.

    Forgot to include the attachment with an example of what I am talking about.
    Attached Files Attached Files

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks - thats very close, only other query is extending the range if more names are entered, I thought something like an imbedded =counta()

    any other thoughts?
    [quote name='mbarron' post='799745' date='24-Oct-2009 23:45']I would use a random number in the A column and use the large function (or small function) to grab the largest or smallest values from the random numbers.

    Forgot to include the attachment with an example of what I am talking about.[/quote]

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this version. It uses a dynamic named range for the VLOOKUP and uses zeros in the A column if the B column is blank. That way the blank names will not be returned since the Rand() function will be greater than zero.
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Actually the dynamic range is not needed since the Large() values in the A column will be limited to the rows where the B column is not blank.

    You can use the formula:
    Code:
    =VLOOKUP(LARGE($A$1:$A$50,ROW()-1),$A$1:$B$50,2,0)

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    That's looks great - cheers
    [quote name='mbarron' post='799752' date='25-Oct-2009 01:17']Actually the dynamic range is not needed since the Large() values in the A column will be limited to the rows where the B column is not blank.

    You can use the formula:
    Code:
    =VLOOKUP(LARGE($A$1:$A$50,ROW()-1),$A$1:$B$50,2,0)
    [/quote]

Posting Permissions

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