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.

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

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

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

Discover MoreReference 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."

2015-12-30 07:29:16

DaveK

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

2013-07-22 04:16:37

DaveK

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

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

2013-07-20 10:16:27

Jim

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 © 2018 Sharon Parq Associates, Inc.

## Comments