Randomly Assigning Names to Items

by Allen Wyatt
(last updated June 10, 2016)


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:


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

    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. ...


Jumping to a Footnote

Jumping to a specific footnote can be very handy if your document has a lot of footnotes in it. Word provides the ...

Discover More

Applying Numbers from the Keyboard

Creating a numbered list is often done by using the tools available on the ribbon. With a bit of preparation you can ...

Discover More

Adjusting Line Spacing

When formatting your document, one of the layout decisions you need to make is how "spread out" your text will be, ...

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)

Removing Dashes from ISBN Numbers

ISBN numbers are used to denote a unique identifier for a published book. If you remove the dashes included in an ISBN, ...

Discover More

Relative References to Cells in Other Workbooks

When you construct a formula and click on a cell in a different workbook, an absolute reference to that cell is placed in ...

Discover More

Returning Zero When a Referenced Cell is Blank

Reference a cell in a macro, and if that cell is blank Excel normally equates that to a zero value. What if you don't ...

Discover More

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

View most recent newsletter.


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. Maximum image size is 6Mpixels. 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 three more than 8?

2015-12-30 07:29:16



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


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.

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

If numNames <= numItems Then
For i = 1 To numNames
x = Int(numItems * Rnd()) + 1
While Range("B" & x).Value <> ""
x = Int(numItems * Rnd()) + 1
Range("B" & x).Value = Range("G" & i).Value
Next i
For i = 1 To numItems
x = Int(numNames * Rnd()) + 1
While Range("H" & x).Value <> ""
x = Int(numItems * Rnd()) + 1
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


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


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?

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

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.