Randomly Assign Employees to Groups of Two Without Repeating

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.
LVL 1
penlandtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glenn RayExcel VBA DeveloperCommented:
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
Glenn RayExcel VBA DeveloperCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
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
mankowitzCommented:
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
aikimarkCommented:
@mankowitz

This is an Access and Excel (zoned) question, not in the SQL Server zone
0
Nick67Commented:
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
penlandtAuthor Commented:
Thank you, that's just what I needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.