Random Name Pairing (Macro or Forumla)

[willhh3]

Hi All:

Hoping for some advise on how to create a list of randomly paired names based on two different positions (director and manager). It's for a duty roster, so the names will repeat to cover a year.

These are the notes I put in the sample spreadsheet:
Goal is to randomly pair Dir to manage to create a list of duty for the week starting in Col E.
There are 14 directors and 28 managers (currently)
Director names will repeat 3-4 times for the year, managers will repeat 3-4 times.
But would prefer directors weren't paired to the same manager; meaning each director manager pairing is unique.
Would like a macro or formula to create the pairings in Col F and G.

I was able to get close by assigning random numbers to each name, but it leave the issue of giving too many to one person and not enough to the other. I can do all this manually, but was hoping for a way to modify. Thoughts?

As always, thank you for any insights.

Whh3

[Pete_UK]

Here is a formula solution, which should also be scalable if the number of directors and/or managers changes.

At the top of columns J and K I've counted the number of each group, and also given the start row for each group, as you have listed them group-wise. Then from J6/K6 down I've listed all possible combinations of Directors and Managers (as numbers), using these formulae:

J6: =IF(ROWS($1:1)>$J$2*$K$2,"",INT((ROWS($1:1)-1)/$K$2)+1)

K6: =IF(J6="","",MOD(ROWS($1:1)-1,$K$2)+1)

The number in cell L2 shows the total number of combinations to expect, so I've copied these formulae down to row 400 (given that they start on row 6). I've listed the names in column L by putting this formula in L6:

=IF(J6="","",INDEX($A:$A,J$3+J6-1))

which can be copied into M6 and then both formulae copied down to row 400.

As there are 28 managers, you can avoid picking the same director or manager in consecutive weeks by choosing from this list every 29 rows, i.e. from row 6, then row 35, then 64 and so on, and I've used column D to produce this number using this formula in D2:

=MOD((ROWS($1:1)-1)*($K$2+1),$J$2*$K$2)+6

Copy down to the bottom of your list of dates. The director's name can be returned in F2 using this formula:

=INDEX(L:L,$D2)

which can be copied into G2 for the manager's name, and then both formulae copied down.

There is no randomness involved, but the distribution ensures an equitable spread throughout the year, as well as ensuring that people are not chosen on consecutive weeks.

Hope this helps.

Pete

[willhh3]

Thanks so much Pete! I think this will definitely work with minimal intervention on my part once is built. Then I'm guessing for 2023 I could shuffle the numbers around to produce a new order. I try not to have the same people work with the same people each year and that there is an equal distribution. Again, thanks for the education!

Let me tinker with this for a few days before I mark it solved. Also curious to see if I get any other suggestions.

[Pete_UK]

If your dates continued down column E (and you could put this formula in E54:

=E53+7

and copy this down to calculate those dates automatically), then you could just copy the formulae in columns D, F and G down to suit, and continue with the allocations. With 392 different combinations, and approximately 50 weeks in a year, this would give you almost 8 years before the allocations would repeat, although I'm sure some of the names would change within that timescale.

Hope this helps.

Pete

[Pete_UK]

Further to this, I did what I suggested in my previous post and extended the dates until they start to repeat (on row 394). This will take you up to July 2029.

When you are ready, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Hope this helps.

Pete

[willhh3]

Ah yes... I wasn't thinking that way but makes perfect sense to extend. Will mark as "Solved" and again thanks!

Cheers
WHH3

[Pete_UK]

You're welcome.

Pete