Solved

Randomly Assign Employees to Groups of Two Without Repeating

Posted on 2014-10-26
8
431 Views
Last Modified: 2014-10-28
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.

Thanks in advance for any help you can offer.
0
Comment
Question by:penlandt
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 24

Accepted Solution

by:
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

by:Glenn Ray
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

by:Glenn Ray
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

Open in new window


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

Open in new window


-Glenn
0
 
LVL 45

Expert Comment

by:aikimark
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 24

Expert Comment

by:mankowitz
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;

Open in new window


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

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40405480
@mankowitz

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

Expert Comment

by:Nick67
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

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now