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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5682) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Sometimes putting together a formula can be a challenge. Part of the process, though, is examining the data with which ...
Discover MoreIf you want to find out the Zodiac sign for a birthdate, there are a number of ways you can do it. This tip provides ...
Discover MoreNeed to figure out how many rows in a worksheet meet two criteria that you specify? Here's how to get the info you desire.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-11-26 15:50:39
J. Woolley
The AssignNames5 macro in my most recent comment below randomly shuffles the List array. My Excel Toolbox now includes the following function to return a range or array with all rows and/or columns randomly shuffled:n    =Shuffle(RangeArray, [RowsOnly], [ColsOnly])nIf RowsOnly is True, columns will remain with their shuffled rows.nIf ColsOnly is True, rows will remain with their shuffled columns.nDefault for both RowsOnly and ColsOnly is FALSE. An error is returned if both are TRUE; otherwise, they apply only when RangeArray is 2D.nSee https://sites.google.com/view/MyExcelToolbox/
2023-11-24 12:25:19
J. Woolley
Re. the AssignNamesX macros in my three previous comments below, here is yet another version. This one is the simplest.nnSub AssignNames5()n    Dim ItemCount, NameCount, ListCount, List, Temp, i, nn    If Range("A2") = "" Or Range("G2") = "" Then Exit Subn    ItemCount = Range("A2").End(xlDown).Row - 1n    NameCount = Range("G2").End(xlDown).Row - 1n    ListCount = IIf(NameCount < ItemCount, ItemCount, NameCount)n    List = Range("G2").Resize(ListCount).Value '2D base-1 column of namesn    List = WorksheetFunction.Transpose(List) '1D base-1 array (row vector)n    'When more items than names, assign blank namesn    For i = NameCount + 1 To ListCount 'Skipped if ListCount = NameCountn        List(i) = ""n    Next in    'Randomly shuffle the values in List arrayn    For i = 1 To ListCountn        n = Int((ListCount * Rnd) + 1) 'Random number 1 to ListCountn        Temp = List(i)n        List(i) = List(n)n        List(n) = Tempn    Next in    'Assign randomized values from List to itemsn    For i = 1 To ItemCountn        Range("B1").Offset(i) = List(i)n    Next in    'Clear any remainder plus one celln    For i = ItemCount + 1 To ListCount + 1n        Range("B1").Offset(i) = ""n    Next inEnd SubnnSee http://www.cpearson.com/excel/ShuffleArray.aspx
2023-11-13 10:01:34
J. Woolley
Re. the AssignNames3 macro in my previous comment below, here is yet another version. This one uses a SortedList that is automatically sorted by its random number Keys.nnSub AssignNames4()n    Const REPEAT_NAMES = False 'True to repeat names; False to assign blanksn    Dim ItemCount, NameCount, ListCount, Key, i, nn    If Range("A2") = "" Or Range("G2") = "" Then Exit Subn    ItemCount = Range("A2").End(xlDown).Row - 1n    NameCount = Range("G2").End(xlDown).Row - 1n    ListCount = IIf(NameCount < ItemCount, ItemCount, NameCount)n    Dim List As Objectn    Set List = CreateObject("System.Collections.SortedList")n    'Associate each name with a random number Key in List sorted by Keyn    For i = 1 To ListCountn        Don            Key = Rnd 'Random number 0 to 1n        Loop While List.ContainsKey(Key) 'Avoid duplicate Keyn        If i > NameCount Thenn            If REPEAT_NAMES Thenn                'When more items than names, randomly repeat namesn                n = Int((NameCount * Rnd) + 1) 'Random number 1 to NameCountn                List.Add Key, Range("G1").Offset(n)n            Elsen                'When more items than names, assign blank namesn                List.Add Key, ""n            End Ifn        Else 'Next name in column Gn            List.Add Key, Range("G1").Offset(i)n        End Ifn    Next in    'Assign randomized names (List is base-0 sorted by random number Keys)n    For i = 1 To ItemCountn        Range("B1").Offset(i) = List.GetByIndex(i - 1)n    Next in    'Clear any remainder plus one celln    For i = ItemCount + 1 To ListCount + 1n        Range("B1").Offset(i) = ""n    Next inEnd SubnnIf there is any difficulty with the following statementn    Set List = CreateObject("System.Collections.SortedList")nenable Microsoft .NET Framework 3.5 using Control Panel as described here: nhttps://learn.microsoft.com/en-us/dotnet/framework/install/dotnet-35-windows
2023-11-08 14:59:22
J. Woolley
Re. the AssignNames2 macro in my previous comment below, if the list of names (NameCount) is less than the list of items (ItemCount), then random names are assigned to the first NameCount items and blank names are assigned to the remaining items. The following AssignNames3 version assigns the blank names for this case randomly as suggested in the Tip's last paragraph.nnSub AssignNames3()n    Const REPEAT_NAMES = False 'True to repeat names; False to assign blanksn    Dim ItemCount, NameCount, NumberCount, i, j, k, tempn    If Range("A2") = "" Or Range("G2") = "" Then Exit Subn    ItemCount = Range("A2").End(xlDown).Row - 1n    NameCount = Range("G2").End(xlDown).Row - 1n    NumberCount = IIf(NameCount < ItemCount, ItemCount, NameCount)n    'Associate a random number with each namen    ReDim tempArray(1 To NumberCount, 1 To 2)n    For i = 1 To NumberCountn        If i > NameCount Thenn            If REPEAT_NAMES Thenn                'When more items than names, randomly repeat namesn                k = Int((NameCount * Rnd) + 1) 'Random number 1 to NameCountn                tempArray(i, 1) = Range("G1").Offset(k)n            Elsen                'When more items than names, assign blank namesn                tempArray(i, 1) = ""n            End Ifn        Elsen            tempArray(i, 1) = Range("G1").Offset(i) 'Name from listn        End Ifn        tempArray(i, 2) = Rnd 'Random number 0 to 1n    Next in    'Bubble sort random number values to randomize associated namesn    For i = 1 To NumberCount - 1n        For j = i + 1 To NumberCountn            If tempArray(i, 2) > tempArray(j, 2) Thenn                For k = 1 To 2n                    temp = tempArray(j, k)n                    tempArray(j, k) = tempArray(i, k)n                    tempArray(i, k) = tempn                Next kn            End Ifn        Next jn    Next in    'Assign randomized namesn    For i = 1 To ItemCountn        Range("B1").Offset(i) = tempArray(i, 1)n    Next in    'Clear any remainder plus one celln    For i = ItemCount + 1 To NumberCount + 1n        Range("B1").Offset(i) = ""n    Next inEnd SubnnNotice the method for randomizing names assures a name is only assigned once as specified in the Tip's first paragraph. But if the following statement n    Const REPEAT_NAMES = False 'True to repeat names; False to assign blanksnis replaced by this statementn    Const REPEAT_NAMES = True 'True to repeat names; False to assign blanksnthen when NameCount is less than ItemCount, names will be repeated randomly to avoid assigning any blank names to the remaining items. REPEAT_NAMES is ignored if NameCount >= ItemCount.
2023-11-06 10:10:39
J. Woolley
The Tip's macro has several issues. Here is an improved version:nnSub AssignNames2()n    Dim ItemCount, NameCount, AssignCount, i, j, k, tempn    If Range("A2") = "" Or Range("G2") = "" Then Exit Subn    ItemCount = Range("A2").End(xlDown).Row - 1n    NameCount = Range("G2").End(xlDown).Row - 1n    'Randomize Namesn    ReDim tempArray(1 To NameCount, 1 To 2)n    For i = 1 To NameCountn        tempArray(i, 1) = Range("G1").Offset(i)n        tempArray(i, 2) = Rnd()n    Next in    'Bubble Sortn    For i = 1 To NameCount - 1n        For j = i + 1 To NameCountn            If tempArray(i, 2) > tempArray(j, 2) Thenn                For k = 1 To 2n                    temp = tempArray(j, k)n                    tempArray(j, k) = tempArray(i, k)n                    tempArray(i, k) = tempn                Next kn            End Ifn        Next jn    Next in    'AssignNamesn    AssignCount = IIf(NameCount > ItemCount, ItemCount, NameCount)n    For i = 1 To AssignCountn        Range("B1").Offset(i) = tempArray(i, 1)n    Next in    k = IIf(NameCount < ItemCount, ItemCount, NameCount)n    For i = AssignCount + 1 To k + 1n        Range("B1").Offset(i) = ""n    Next inEnd SubnnThis macro assumes the following:n1. Row 1 has column headings assigned by the user.n2. The list of items is from cell A2 to the first blank cell in column A.n3. The list of names is from cell G2 to the first blank cell in column G.n4. The list of names assigned to items is in column B starting at cell B2 overwriting any previous content including blank cells down to one row below the list of items and names.
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 © 2026 Sharon Parq Associates, Inc.
Comments