Best Answer:
It would have been helpful to know what version of MS-Excel you are using, along with the range of Student-IDs and Locker-Numbers. Even an example of the Student-IDs and Locker-Numbers.

So I will have to assume numbers 1 through 100 are used for both sets.

Enter a value of "1" in cell A1, and "2" in cell A2. Then select those two cells and use the Fill Handle to drag down to cell A100. This will be the Student-IDs.

Now select all of the cells from B1 through B100 inclusive. While they are selected, type in the following as an array formula:

= RangeRandomize( $A$1:$A$100 )

An "array formula" requires you to use the key combination of CTRL+SHIFT+ENTER at the point of actually entering the formula. If done correctly, a set of curly braces will surround the whole formula (the braces can not be entered by hand).

For the above function to work, you will need to add the following VBA code into a module for this workbook:

Option Explicit

Function RangeRandomize(rng)

Dim V() As Variant, ValArray() As Variant

Dim CellCount As Double

Dim i As Integer, j As Integer

Dim r As Integer, c As Integer

Dim Temp1 As Variant, Temp2 As Variant

Dim RCount As Integer, CCount As Integer

Randomize

CellCount = rng.Count

If CellCount > 1000 Then

RangeRandomize = CVErr(xlErrNA)

Exit Function

End If

RCount = rng.Rows.Count

CCount = rng.Columns.Count

ReDim V(1 To RCount, 1 To CCount)

ReDim ValArray(1 To 2, 1 To CellCount)

For i = 1 To CellCount

ValArray(1, i) = Rnd

ValArray(2, i) = rng(i)

Next i

For i = 1 To CellCount

For j = i + 1 To CellCount

If ValArray(1, i) > ValArray(1, j) Then

Temp1 = ValArray(1, j)

Temp2 = ValArray(2, j)

ValArray(1, j) = ValArray(1, i)

ValArray(2, j) = ValArray(2, i)

ValArray(1, i) = Temp1

ValArray(2, i) = Temp2

End If

Next j

Next i

i = 0

For r = 1 To RCount

For c = 1 To CCount

i = i + 1

V(r, c) = ValArray(2, i)

Next c

Next r

RangeRandomize = V

End Function

.

Source(s):