Randomly Assigning Names to Items

by Allen Wyatt
(last updated June 8, 2018)


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.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

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


Leaving a Cell Value Unchanged If a Condition Is False

Ever want the IF function to only return a value if the condition it is testing is true, and not if the condition is ...

Discover More

Hyperlinks in Comments

Need to add a hyperlink to a comment? It's easy to do by following the steps outlined in this tip.

Discover More

Checking for Text

Need to figure out if a particular cell contains text? You can use the ISTEXT function to easily return this bit of trivia.

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Figuring Out the Low-Score Winner

Need to figure out the lowest score in a range of scores? Here's the formulas to get the information you need.

Discover More

Counting Cells According to Case

Text placed in cells can either be lowercase, uppercase, or a mixture of the two. If you want to count the cells based ...

Discover More

Calculating an IRR with Varying Interest Rates

You might wonder how you can calculate an IRR (internal rate of return) when the person repaying the loan pays different ...

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 3 - 3?

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.