+ Reply to Thread
Results 1 to 7 of 7

Random Names

  1. #1
    Registered User
    Join Date
    02-02-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Exclamation Random Names

    Hi,

    I was trying to figure out how to randomly select name from list; I tried with RANDBETWEEN() but didn't get the desired result.

    I have a list of unique names in a column and I want to assign one name each from the same list without any duplicates.

    Example:
    Col A
    a
    b
    c
    d
    e
    f

    I want a formula to randomly select name from the list and assign to each cell without duplicates and if 'd' is assigned to 'a', then 'a' has to been assigned to 'd', not other name from list.

    Result:
    Col A Col B
    a d
    b e
    c f
    d a
    e b
    f c

    Kindly help me on this.

    Thanks in advance and all your help will be much appreciated.
    Pradeep

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Random Names

    Hello
    Build the C column with the following formula in C1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and copy it to the remaining cells (C2:C6)
    In column B use the following formula in B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and copy it to the remaining cells (B2:B6)
    I hope this helps
    See the file Random Names.xlsx

  3. #3
    Registered User
    Join Date
    02-02-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Question Re: Random Names

    Hi Jose,

    Thanks for your quick response.

    I tired your formula, but not getting as desired.

    Result after formula:
    A B 0.719
    B E 0.388
    C A 0.816
    D D 0.763
    E C 0.705
    F F 0.876

    In the above result, B is assigned to A, but B has a different name assigned to it, i.e. E not A. Same way D is assigned to D only and also F.

    I want to select random name without repetition and one name should not be assigned to more than one name.

    If A = B then B = A
    If C = E then E = C
    If D = F then F = D


    Thanks your help.

    Pradeep

  4. #4
    Forum Contributor
    Join Date
    08-07-2015
    Location
    AB, Canada
    MS-Off Ver
    Excel 2013 x64
    Posts
    132

    Re: Random Names

    This isn't the most elegant solution, but I think it does what you're looking for.

    Essentially it (1) assigns a random value to each name, (2) orders those random values, (3) returns the associated letter in the new, random order, (4) offsets the randomized list by 1 to ensure you never match with the same letter twice, and (5) matches the original ordered names with the randomized, offset by one, set of names.
    Attached Files Attached Files
    Last edited by ThirdFret; 08-14-2015 at 10:39 AM.

  5. #5
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Random Names

    Hi

    The file contains the solution you want (I suppose).
    In Sheet2 is a generalized solution.
    Best regards

    Random Names(2).xlsx

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Random Names

    Here is a sheet that creates a Bingo form. Use the same principle shown here to make your listing.
    +

    Here are the formulas used

    Data Range
    A
    B
    C
    D
    6
    Alabama
    =RAND()
    =RANK(B6,$B$6:$B$52,1)+COUNTIF($B$6:$B6,B6)-1
    =OFFSET($A$6,C6-1,0)

    copy the formulas down.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  7. #7
    Registered User
    Join Date
    02-02-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: Random Names

    Jose, This worked like a charm. Thank you so much.

+ 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] Draw random names from list and then remove, carry on till no names left in list
    By dave1983 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-26-2014, 12:14 PM
  2. [SOLVED] Replace all names with random names
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-28-2012, 05:47 PM
  3. Random names to random tasks
    By aquinlan in forum Excel General
    Replies: 8
    Last Post: 06-18-2010, 05:57 PM
  4. random names from one sheet to another
    By Sheepkin_Coat in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2010, 02:18 PM
  5. Random Names
    By DCSwearingen in forum Excel General
    Replies: 1
    Last Post: 06-19-2006, 12:25 PM
  6. Getting a Random list of names
    By Vijaybidappa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-13-2005, 05:28 PM
  7. random non-repeating names
    By rivet in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-18-2005, 10:06 AM

Tags for this Thread

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