Randomly Assigning Names to Items

by Allen Wyatt
(last updated June 10, 2016)

7

Gary has two lists in a worksheet. One of them, in column A, contains a list of surplus items in our company and the other, in column G, contains a list of names. There is nothing in columns B:F. Gary would like to assign names, randomly, to the list of items. Each name from column G should be assigned only once. If there are more names than items, then some names won't get used. If there are fewer names than items, then some items won't have associated names.

There are a couple of ways that this can be done. Perhaps the easiest, though, is to simply assign a random number to each item in column A. Assuming that the first item is in cell A1, put the following in cell B1:

=RAND()

Double-click the fill handle in cell B1, and you should end up with a random number (between 0 and 1) to the right of each item in column A.

Now, select all the cells in column B and press Ctrl+C to copy them to the Clipboard. Use Paste Special to paste values right back into those cells in column B. (This converts the cells from formulas to actual static values.)

Sort columns A and B in ascending order based on the values in column B. If you look across the rows, you'll now have items (column A) associated randomly with a name (column G).

Even though it is not necessary, you could also follow these same steps to add a random number to the right of each name and then sort the names. (I say it isn't necessary because randomizing the items should be enough to assure that there are random items associated with each name.)

The technique discussed so far works great if you have to do the random pairing only once in a while. If you need to do it quite often, then a macro may be a better approach. There are, of course, many different macro-based approaches you could use. The following approach assumes the item list is in column A and the name list in column G. It also assumes that there are header cells in row 1 for each column.

Sub AssignNames()
    Set srItems = Range("A2").CurrentRegion
    Set srNames = Range("G2").CurrentRegion
    NameCount = srItems.Rows.Count - 1
    ItemCount = srNames.Rows.Count - 1

    'Randomize Names
    ReDim tempArray(NameCount, 2)
    For x = 0 To NameCount - 1
        tempArray(x, 0) = Range("G2").Offset(x, 0)
        tempArray(x, 1) = Rnd()
    Next x

    'Bubble Sort
    For i = 0 To NameCount - 2
        For j = i To NameCount - 1
            If tempArray(i, 1) > tempArray(j, 1) Then
                tempItem = tempArray(j, 0)
                tempName = tempArray(j, 1)
                tempArray(j, 0) = tempArray(i, 0)
                tempArray(j, 1) = tempArray(i, 1)
                tempArray(i, 0) = tempItem
                tempArray(i, 1) = tempName
            End If
        Next j
    Next i

    'AssignNames
    Range("B2") = "Assigned"
    AssignCount = NameCount
    If NameCount > ItemCount Then AssignCount = ItemCount
        For x = 0 To AssignCount
        Range("B2").Offset(x, 0) = tempArray(x, 0)
    Next x
End Sub

If there are more names than items the macro randomly assigns names to items. If there are more items than names it randomly assigns some items to names and randomly leaves "holes" (items without names). It stores them in column B, overwriting whatever was there.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5682) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Printing a Full Style Sheet

Word supports the use of styles (they are very powerful), but it doesn't provide a way to get a full-featured style sheet ...

Discover More

Number Formatting Shortcuts

Keyboard shortcuts can save time and make developing a workbook much easier. Here's how to apply the most common of Excel's ...

Discover More

Remembering Workbook Settings from Session to Session

You can spend a lot of time getting your workbook to look "just right." Wouldn't it be great if Excel was able to remember ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

MORE EXCELTIPS (RIBBON)

Returning the Minimum of Integers of a Range

If you have a range of numbers that contain both integers and decimal numbers, you may have a need to determine the minimum ...

Discover More

Entering Formulas in Excel

The primary way you signify that you are entering a formula is to start a cell entry with an equal sign. The equal sign is ...

Discover More

Moving the House Number to Its Own Cell

Excel is great at manipulating data, but sometimes it is difficult to figure out the best way to do the manipulation. This ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 8?

2015-12-30 07:29:16

DaveK

Kevin,

I wrote the code a long time ago so it isn't fresh in my mind! However, re-reading it, I found that:

Just after column B is cleared, the If statement compares the number of names and the number of items. If there are fewer names, then the code immediately after the IF statement is executed. If there are more names, the code "drops" to the Else statement and continues from there.

In other words, the code finds the shorter list and runs until it is used up.

So, if there are more items than names, every name will have an item beside it, but not all items will be allocated. Similarly, if there are more names than items, then every item will be allocated to a name and some names will have no allocation.

I recreated the problem in a new workbook and the code works fine. Let me know if you need anything further. Always happy to help.


2015-12-29 14:00:59

Kevin Wright

Would you be able to make this VBA code work for me the opposite way, where there are more items than names? That way each name is assigned an item randomly.


2013-07-22 04:16:37

DaveK

Here's a shorter (and simpler?) macro that will produce a result for this problem.

What the macro does:
a) Clear Column B (where the names will be written).
b) Go through the shorter list in sequence, so it is processed only once. The longer list is processed randomly.
c) As each name is allocated, a "used" marker is placed beside it to prevent it from being picked again.
d) When all selections are made, the markers are cleared.
e) Clearing Column B at the start and Column H at the end allows the macro to be run again and again if desired.

Notes:
Cell C2 has the formula =COUNTA(A:A)
Cell I2 has the formula =COUNTA(G:G)

Macro code:

Sub AssignNames()

Dim numItems As Integer
Dim numNames As Integer
Dim x As Integer
Dim i As Integer

numItems = Range("C2").Value
numNames = Range("I2").Value

Range("B:B").Clear
If numNames <= numItems Then
For i = 1 To numNames
x = Int(numItems * Rnd()) + 1
While Range("B" & x).Value <> ""
x = Int(numItems * Rnd()) + 1
Wend
Range("B" & x).Value = Range("G" & i).Value
Next i
Else
For i = 1 To numItems
x = Int(numNames * Rnd()) + 1
While Range("H" & x).Value <> ""
x = Int(numItems * Rnd()) + 1
Wend
Range("B" & i).Value = Range("G" & x).Value
Range("H" & x).Value = "used"
Next i
End If
Range("H1:H" & numNames).Clear
End Sub


2013-07-21 19:26:09

Craig

Colin & Zvi - instead of just =RAND(), use something like =RAND()^RAND() or =RAND()/RAND()


2013-07-20 23:48:50

Zvi Davis

To Colin:
Even if you do get 2 values that are exactly the same (a low probability event, since Excel uses 16 digit accuracy), when sorting, they will be randomly sorted, so it doesn't matter that they are the same


2013-07-20 23:38:20

Colin Delane

Whilst the probability may be as low as that for winning the lotto, using =RAND() in each cell could produce more than one result of exactly the same value. Accordingly, some mechanism is required to ensure that this does not happen.


2013-07-20 10:16:27

Jim

If you really don't care which name is paired with which item, then why not simply pair A1 with G1, A2 with G2, and so on. In other words, what added benefit do the algorithms provide?


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share