Random Quarter Generator - No Duplicates

Hassrona

New Member
Joined
Sep 26, 2018
Messages
13
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 ?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

GJSBED

Board Regular
Joined
May 3, 2016
Messages
91
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.

straightRandom
16
210
38
49
52
64
79
87
93
107
116

<colgroup><col span="2"></colgroup><tbody>
</tbody>

and after sorting by random

straightRandom
52
93
64
16
116
87
107
38
49
79
210

<colgroup><col span="2"></colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,099,806
Messages
5,470,890
Members
406,733
Latest member
darzu

This Week's Hot Topics

Top