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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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,102,043
Messages
5,484,368
Members
407,437
Latest member
alfaroM

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top