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.

**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!

Formulas are the heart of using Excel, and formulas often refer to ranges of cells. How you insert cells into the referenced ...

Discover MoreFormulas are made up of operands that separate a series of terms acted upon by the operands. You may want to know, for some ...

Discover MoreWant to be able to take information that is in one cell and match it to data that is contained in a table within a worksheet? ...

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

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.

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

Copyright © 2017 Sharon Parq Associates, Inc.

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?