Limit duplications in Excel Column

Help
1

Hi Forum,

I´m stuck in a little problem.
I have a Excel sheet with 40 Names (Column A) and I want to designate every of the 40 Names a random Number between 1-10 in Column B.
The state now is that I get for every name a random number, but for example number 4 is 7 times and number 8 just 2 times assigned to a unique name.
I want to limit the duplications to maximum of 4 that I have in the end 10teams out of 4people randomly generated.

Anyone has an idea how to solve that?
Thanks a lot!

2

You can generate and array that looks like

Arr = [1,1,1,1,2,2,2,2,3,3,…,10,10,10,10]

Then loop through the array element by element and randomly assign that number to an empty position

3

Do you have an example for that?
I dont know how to assign the number randomly to an empty position.

Thanks a lot!

4

You could write a little loop that generates a number 1-40. Say, it generated 5, you would then check A5. If A5 is empty, you copy the first number in. If it’s not empty, you generate another number (x) until you find that A(x) is empty.

Repeat that for every item in your number array and you’ll have a random assortment.

Alternatively, you could just randomize your number array and drop them in one by one.