Random generator with no duplicates...

[slim6y]

In Column A I have a list of 8 team names.
In column AB I have a list of 28 weeks written in date format.

In column B I'd like to chose one week from the 28 weeks listed in AB at random. But B2 - B9 can not have the same date (ie could not have two dates for the 19th of April).

How exactly is that done?

Thank you kindly in advance (ps - I looked but I couldn't see something similar to what I was asking).

Alternatively - is it possible to have in Column AA the 8 team names. In Column AB the 28 weeks. In Column A a random team picked from column AA and in Column B a random date picked from Column AB. Both column A and Column B can not have duplicates... There's 28 weeks with 8 teams - therefore I only need 8 unique weeks.

I look forward to some answers

 

[Peter_SSs]

See if you can adapt from this thread.

 

[slim6y]

See if you can adapt from this thread.

I can I guess... But I'm having troubles with the idea that there's a 'specific' number - and I'm a little noob confused about the random column of numbers in H.

My problem is I have a 'lucky dip' arrangement. There's 28 dates that must be chosen from but just 8 teams - and each team needs to just be assigned one date.

The team names are in Column A already - all I need to do is have a random date selected from 28 possible dates (located in column AB) that can not be duplicates.

The one you've shown is good - and I will look at modifying it - but I still can't work out the random column of H or the reason for having 11 in A1...

 

[Peter_SSs]

OK, if you don't want the dates to be changing all the time, I think we should go for a macro approach.

The code below lets you nominate where the data is and how big it is.
I've used 3 helper columns and you can nominate in the code where the first of these three consecutive available columns are. Currently it is set to "BA" so columns BA:BC are used as helper columns by my code.
If you need help with how to implement the code post back.

Sub AssignRandomDates()
  Const FirstTeamCell As String = "A2"
  Const NumTeams As Long = 8
  Const FirstDateCell As String = "AB2"
  Const NumDates As Long = 28
  Const SpareCol As String = "BA" '<- First of 3 available spare columns
  
  Application.ScreenUpdating = False
  With Range(SpareCol & 1).Resize(NumDates)
    .Value = Range(FirstDateCell).Resize(NumDates).Value
    With .Offset(, 1)
      .Formula = "=RAND()"
      .Value = .Value
    End With
    With .Offset(, 2)
      .FormulaR1C1 = "=RANK(RC[-1],R1C[-1]:R" & NumDates & "C[-1])+COUNTIF(R1C[-1]:RC[-1],RC[-1])-1"
      .Value = .Value
    End With
    .Resize(, 3).Sort Key1:=.Cells(1, 3), Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    Range(FirstTeamCell).Resize(NumTeams).Offset(, 1).Value = .Resize(NumTeams).Value
    .Resize(, 3).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

My last post's code is more complicated than needed. This should do just as well. Only need to helper columns, not 3.

Sub AssignRandomDates()
  Const FirstTeamCell As String = "A2"
  Const NumTeams As Long = 8
  Const FirstDateCell As String = "AB2"
  Const NumDates As Long = 28
  Const SpareCol As String = "BA" '<- First of 2 available spare columns
  
  Application.ScreenUpdating = False
  With Range(SpareCol & 1).Resize(NumDates)
    .Value = Range(FirstDateCell).Resize(NumDates).Value
    With .Offset(, 1)
      .Formula = "=RAND()"
      .Value = .Value
    End With
    .Resize(, 2).Sort Key1:=.Cells(1, 2), Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    Range(FirstTeamCell).Resize(NumTeams).Offset(, 1).Value = .Resize(NumTeams).Value
    .Resize(, 2).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

 

[slim6y]

My last post's code is more complicated than needed. This should do just as well. Only need to helper columns, not 3.

Sub AssignRandomDates()
  Const FirstTeamCell As String = "A2"
  Const NumTeams As Long = 8
  Const FirstDateCell As String = "AB2"
  Const NumDates As Long = 28
  Const SpareCol As String = "BA" '<- First of 2 available spare columns
  
  Application.ScreenUpdating = False
  With Range(SpareCol & 1).Resize(NumDates)
    .Value = Range(FirstDateCell).Resize(NumDates).Value
    With .Offset(, 1)
      .Formula = "=RAND()"
      .Value = .Value
    End With
    .Resize(, 2).Sort Key1:=.Cells(1, 2), Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    Range(FirstTeamCell).Resize(NumTeams).Offset(, 1).Value = .Resize(NumTeams).Value
    .Resize(, 2).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

I'll check tomorrow - I used the other example and it works - which is nice