Random Quarter Generator - No Duplicates

[Hassrona]

Hi,

I am relatively new to excel vba and I am currently trying to resolve an issue. I have been asked to generate an excel file with randomly assigned quarters to a business unit. There are 11 business units and 11 quarters in scope - the end result being a distinct quarter for each business unit every time the macro is run. So far I have developed a macro which assigns a quarter to each unit, however there are duplicates in the randomly assigned quarters. I need the macro to run with no duplicates in the assigned quarters.

Eg

Business Unit (Column A) Quarter (Column B)
(Name) Q4/2018
(Name) Q3/2019

etc

My code looks like this currently -

Range("B2").Value = [index({"Q3/2018", "Q4/2018", "Q1/2019", "Q2/2019", "Q3/2019", "Q4/2019", "Q1/2020", "Q2/2020", "Q3/2020", "Q4/2020", "Q1/2021", "Q2/2021", randbetween(1,11))]

The code repeats with this same format with the only aspect changing is the Range, eg Range("C2"). Value etc all the way to Range("B12").Value

Any ideas on how to make sure the 11 assigned quarters are unique each time without any duplicates ?

 

[GJSBED]

You want two columns one that is numbered 1 to 11 in order for each Business Unit.

In a column next door to that do a straightforward randbetween, copy and paste values the outcome of that randbetween then sort the two columns by that random column, and your column that you numbered 1 to 11 in order is now all jumbled up and has no duplicates.

[TABLE="width: 178"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]straight[/TD]
[TD]Random[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

and after sorting by random

[TABLE="width: 178"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]straight[/TD]
[TD]Random[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]