Solved

# Randomly Assign Employees to Groups of Two Without Repeating

Posted on 2014-10-26
431 Views
Good morning Experts!  I hope this is a simple question and that I'm just having some trouble wrapping my head around it.  I need to create a simple application (either in Excel or Access - though I suspect Access will be the better choice) that will allow me to randomly split up a group of 18 employees (that number will change over time) into working groups of two each day.  No two employees should work together again until every employee has worked one day with every other employee.  I'm able to create the tables and the forms that I need to do this, and I'm able to write the VBA code to make it happen if I can just get the formula for doing this straight in my head.

0
Question by:penlandt
• 2
• 2
• 2
• +2

LVL 24

Accepted Solution

mankowitz earned 500 total points
ID: 40405051
Here is some pseudocode.

1. Make a list of all possible combinations. This will generate 18C2 pairs (should be a list of 153 rows).
2. Start from the beginning of your list and keep selecting pairs until all employees are represented.
3. Publish today's schedule and remove those nine pairs from the list.
4. Every day, repeat that process (step 2) until there are no rows left in your list (should be day 17). Then start from the beginning (line 1)

Remember that if you change the number of people, you will also have to start at step 1.
0

LVL 27

Expert Comment

ID: 40405105
You may be better-served by looking for round robin algorithms or similar.

This similar problem was tackled not to long ago here in EE and a good solution was derived for 12 persons.  It's scalable and explained pretty well in the solution by ozo.
Excel Grid Matching Game

-Glenn
0

LVL 27

Expert Comment

ID: 40405137
Not an algorithm, but here are the 18-person solutions:
``````1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
2	1	18	17	16	15	14	13	12	11	10	9	8	7	6	5	4	3
3	4	1	2	18	17	16	15	14	13	12	11	10	9	8	7	6	5
4	6	5	1	3	2	18	17	16	15	14	13	12	11	10	9	8	7
5	8	7	6	1	4	3	2	18	17	16	15	14	13	12	11	10	9
6	10	9	8	7	1	5	4	3	2	18	17	16	15	14	13	12	11
7	12	11	10	9	8	1	6	5	4	3	2	18	17	16	15	14	13
8	14	13	12	11	10	9	1	7	6	5	4	3	2	18	17	16	15
9	16	15	14	13	12	11	10	1	8	7	6	5	4	3	2	18	17
10	18	17	16	15	14	13	12	11	1	9	8	7	6	5	4	3	2
11	3	2	18	17	16	15	13	13	12	1	10	9	8	7	6	5	4
12	5	4	3	2	18	17	16	15	14	13	1	11	10	9	8	7	6
13	7	6	5	4	3	2	18	17	16	15	14	1	12	11	10	9	8
14	9	8	7	6	5	4	3	2	18	17	16	15	1	13	12	11	10
15	11	10	9	8	7	6	5	4	3	2	18	17	16	1	14	13	12
16	13	12	11	10	9	8	7	6	5	4	3	2	18	17	1	15	15
17	15	14	13	12	11	10	9	8	7	6	5	4	3	2	18	1	16
18	17	16	15	14	13	12	11	10	9	8	7	6	5	4	3	2	1
``````

Or, shown in weekly pairs:
``````1	3	4	5	6	7	8	9	10
2	18	17	16	15	14	13	12	11

1	4	5	6	7	8	9	10	11
3	2	18	17	16	15	14	13	12

1	5	6	7	8	9	10	11	12
4	3	2	18	17	16	15	14	13

1	6	7	8	9	10	11	12	13
5	4	3	2	18	17	16	15	14

1	7	8	9	10	11	12	13	14
6	5	4	3	2	18	17	16	15

1	8	9	10	11	12	13	14	15
7	6	5	4	3	2	18	17	16

1	9	10	11	12	13	14	15	16
8	7	6	5	4	3	2	18	17

1	10	11	12	13	14	15	16	17
9	8	7	6	5	4	3	2	18

1	11	12	13	14	15	16	17	18
10	9	8	7	6	5	4	3	2

1	12	13	14	15	16	17	18	2
11	10	9	8	7	6	5	4	3

1	13	14	15	16	17	18	2	3
12	11	10	9	8	7	6	5	4

1	14	15	16	17	18	2	3	4
13	12	11	10	9	8	7	6	5

1	15	16	17	18	2	3	4	5
14	13	12	11	10	9	8	7	6

1	16	17	18	2	3	4	5	6
15	14	13	12	11	10	9	8	7

1	17	18	2	3	4	5	6	7
16	15	15	13	12	11	10	9	8

1	18	2	3	4	5	6	7	9
17	16	15	14	13	12	11	10	8

1	2	3	4	5	6	7	8	9
18	17	16	15	14	13	12	11	10
``````

-Glenn
0

LVL 45

Expert Comment

ID: 40405260
If you put the employees in a table with an autonumber field, you should be able to generate your schedule by joining the table to itself where the autonumber field in the left side table does not equal the autonumber in the right side table.

This query result (LeftID, RightID) should populate another table, let's call it Sched.  The Sched table should also have a number column to indicate the number of times that combination has worked together.  This will keep you from repeating until everyone has the same count.

Put a unique index on the two ID columns in the Sched table.

When you add someone, rerun your original query and push the data to the Sched table.  Only the new (people combination) rows will be added due to the unique index.
0

LVL 24

Expert Comment

ID: 40405474
ok, so I broke down and wrote some mysql code. You can probably adapt this to access or sql server:

``````create table persons (id int, lastname varchar(25));
insert into persons (id, lastname) values
(1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five'),
(6, 'six'), (7, 'seven'), (8, 'eight'), (9, 'nine'), (10, 'ten'),
(11, 'eleven'), (12, 'twelve'), (13, 'thirteen'), (14, 'fourteen'), (15, 'fifteen'),
(16, 'sixteen'), (17, 'seventeen'), (18, 'eighteen');

create table pairs (pairid int not null auto_increment,
id1 int,
id2 int,
cnt int default 0,
primary key (pairid));

alter table pairs add unique index ix1 (id1, id2);

insert ignore pairs (id1, id2)
select least(p1.id, p2.id), greatest(p1.id, p2.id)
from persons p1 join persons p2 where p1.id <> p2.id;
``````

here is the sp
``````CREATE DEFINER=`root`@`localhost` PROCEDURE `getpairs`()
begin
declare pair int;
declare pair1 int;
declare pair2 int;
declare continue_loop int default 1;
drop table if exists person2;
drop table if exists newpairs;
CREATE TABLE person2 AS (SELECT id,0 used FROM persons);
create table newpairs (pairid int);
while (continue_loop>0) do
select pairid into pair from pairs where
id1 in (select id from person2 where used=0)
and id2 in (select id from person2 where used=0) order by cnt limit 1;
update pairs set cnt = cnt + 1 where pairid=pair;
update person2 set used=1 where id = (select id1 from pairs where pairid=pair);
update person2 set used=1 where id = (select id2 from pairs where pairid=pair);
insert newpairs (pairid) values (pair);
select count(*) into continue_loop from person2 where used=0;
-- select concat('found ', pair, ' ', continue_loop);
end while;
select p2.id, p2.lastname, p1.id, p1.lastname from newpairs n join pairs p on (p.pairid=n.pairid)
join persons p1 on (p1.id=p.id1) join persons p2 on (p2.id=p.id2);
end
``````
0

LVL 45

Expert Comment

ID: 40405480
@mankowitz

This is an Access and Excel (zoned) question, not in the SQL Server zone
0

LVL 26

Expert Comment

ID: 40407436
randomly split up a group of 18 employees
No two employees should work together again until every employee has worked one day with every other employee
that number will change over time

If you really need all those requirements, that'll make for some fun and interesting code, as well as table structure!
-that number will change over time
I trust you add folks in pairs, and that there are presently an even number of folks employed?
-randomly split up
So that pattern should NOT repeat after everyone has worked together?
-work together again
That will mean that the last day of a cycle will be determinable in advance, I trust that will be okay?

Given those constraints, something could get built.
You'd need an employees table.
You'd need a present matches table
You'd need a past matches table
You'd need the code
1. reseed the random generator
2. check the past matches table to see if everyone had already worked together, and then archive or clear that table
3. check the present matches table to see if the randomly drawn pair are already in the present dance, or have danced together in the past.
4. commit a good pairing to the present matches table, or redraw if they have dance partners already or danced in the past.
5. when everyone's dance card is filled, commit the present matches table to the past matches table and then clear it getting ready for the next dance.

Sound like what you want?
0

LVL 1

Author Closing Comment

ID: 40410006
Thank you, that's just what I needed.
0

## Featured Post

### Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.