Random assignment

[Pierre]

Hello,

I'm looking for a way to assign randomly workers to a workstation. Workers are divided in 2 teams, so if I have 18 workers in team 1, they should be assigned randomly to position 1 to 18, and if I have 19 workers in team 2, they should be assigned to position 19 to 37.

I'd need a macro as with formula I have 2 issue: always recalculating, and it may assign 2 people to the same position, which I do not want (I need only 1 worker per position).

Can anyone help?

Thank you!

 

[BobBridges]

I would use Collections for this. Here's some pseudocode:

Positions = New Collection 'this collection will hold the final assignments
For each team
  Team = New Collection 'this collection holds the members of the next team
  Load the team members into Team
  Do 'for each member of the team
    nmbr = Collection.Count 'the number of members still unassigned in this team
    vp = Rnd * nmbr + 1 'pick a random member of the team to be assigned the next position
    Set ombr = Team(vp)
    Positions.Add ombr 'the member is now assigned to the next position in Positions
    Team.Remove vp 'remove the assigned member from the Team collection
    Loop While nmbr > 1 'stop when you've emptied the Team collection
  Next team '...and go on to the next team

 

[Pierre]

Hello @BobBridges,

Thank you for your message, I appreciate your answer but as I'm not very good at VBA, this code is not really understandable for me...

Positions = New Collection 'this collection will hold the final assignments

??

How do you define the team? And the number of people in teams?

If you can define a bit more, that would be great!

Thanks!

 

[BobBridges]

A collection, in VBA, is like a one-dimensional array with two big differences: 1) You don't have to know how big to make it ahead of time; you can add or remove members of the collection on the fly, and 2) in addition to referring to items in the collection by number, like an array, you can also key them on a string value. That second difference is very handy, but I'm not making use of it in this case.

As for how you define the team, that depends on how you define the team. I presume you have the teams and their members listed somewhere in Excel, right? So your program has to read the table(s) where you have the teams defined, and put the data on each member into the Teams collection.

But here I begin to suspect that when you say "not very good at VBA", what you really mean is "I've never written a program in my life, in any language, and have no idea how to go about it". Just how much programming have you done before? Because if you need to, we can start from the beginning and I can coach you. But I don't know how much you already know.

 

[Pierre]

Regarding the team, I have a list of worker (column A in tab Teams) and their respective team (CE1 or CE2) in column B

About VBA, to be honest I'm not able to write anything "from the top of my head" but I'm usually able to pick some bits of code here and there and am able to understand them and put them together to make a running macro.

Thank you for your understanding

 

[Logit]

Cross posted : https://www.mrexcel.com/board/threads/assign-random-work-position-with-vba.1214630/

 

[BobBridges]

Hm. Well, some folks (and some of them are here at Chandoo) will be willing to just write the program for you. Me, I'm more interested in teaching you how you can do it for yourself, a piece at a time so that eventually you will be able to write something "from the top of your head". Interested? If so, contact me via email (I'm at robhbridges on gmail) and we'll see how far you can get.

 

[vletm]

Pierre
Please reread Forum Rules, those are for You too

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[Pierre]

@vletm: I apologize for this, I'll make sure to be careful respecting the rules.

Thanks to Logit's answer, I was able to modify his code and have a macro that works exactly as I needed.

 

[p45cal]

A different approach.
There's actually no need for a macro at all since all it does is sort the data on the Tables sheet.
You won't get duplicates, nothing will move about until you do some more sorting.
I wasn't sure how to differentiate between CE1 and CE2, I couldn't find anything in the original sheet which did that. If I'm mistaken it wouldn't be difficult to tweak.