Like so...this will work for any length of data you enter into columns A:B:
Option Explicit
Sub CreateTaskList()
'Author: Jerry Beaucaire
'Date: 6/17/2010
'Summary: Create a randomized list of names and tasks
Dim LR As Long
Application.ScreenUpdating = False
Range("D2:F" & Rows.Count).ClearContents
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("C2:C" & LR).FormulaR1C1 = "=RAND()"
With Range("D2:D" & LR)
.FormulaR1C1 = _
"=INDEX(R2C1:R" & LR & "C1, MATCH(SMALL(R2C3:R" & LR & "C3, ROW(R[-1]C[-3])), R2C3:R" & LR & "C3, 0))"
.Value = .Value
End With
With Range("E2:E" & LR)
.FormulaR1C1 = _
"=INDEX(R2C2:R" & LR & "C2, MATCH(LARGE(R2C3:R" & LR & "C3, ROW(R[-1]C[-3])), R2C3:R" & LR & "C3, 0))"
.Value = .Value
End With
Range("C:C").Clear
Application.ScreenUpdating = True
End Sub
Bookmarks