Rearrange data into other columns

[Ann]

I have a spreadsheet I use to assign our committee audit areas. The same
group of people is rearranged and put into the other columns (per month). It
is rearranged in no particular order, but I attempt to ensure that the same
person does not have to audit the same area two months in a row. Currently,
I manually create this. It looks similiar to this:

Area to audit May June July ...
East Side Nancy
West Side Ann
South Side Joe
North Side Jamie
Office Ruth
Metals Sherry
Warehouse Becky

Is there a way to rearrange the data for each month so that I'm not spending
hours attempting to ensure that everyone is seeing each area?

 

[JLatham]

Without getting overly complex and trying to randomize everything and then
check for duplicate entries for 2 months in a row, how about we simply rotate
the people each month: taking the top of the list for a month and putting
that person at the bottom of the list and moving everyone else up one 'row'.

To do this with formulas, assuming "Area to Audit" is in column A and May is
in column B, then under June, put this formula next to Nancy (in May)
=B3
that should cause Ann to show up auditing East Side in June
'fill' that formula down through Metals : Sherry
Then next to Warehouse : Becky, in June column put
=B2
which should pull Nancy to the bottom of the list.

You can now fill these formulas to the right under later months and the
rotation will be done automatically for you.

Search Excel Help for "fill data" for help doing that if you're unfamiliar
with it. If the 'help' doesn't turn out to be much, then post back.

 

[Ann]

Thank you for your reply. We have tried what you suggested with the perons
on our team, but the team suggested that they get to work with others so that
they can learn from them. Any suggestions to make it more random? I'm at a
loss.

 

[JLatham]

Well, I'm a little confused - are the people listed all of the people involved?

You mention team and working with others - what/who makes up a team?

For now, assuming that these are all the people involved, there is the
possibility of coming up with a macro that would randomly rearrange the
folks, making sure that one or two things don't happen in any two months:

Easy - make sure same person doesn't get the same area 2 months in a row.
More difficult - not only not the same area 2 months in a row, but not
"next" to same 2 people they were the month before.

 

[Ann]

There are a total of seven areas we need to audit and normally fourteen (but
could be up to 18 people) on the team. The "more difficult" choice (sorry!!)
is the answer I'm looking for. I used to be able to write macros, but with
all the shortcuts on 2007, I haven't used it in a long time. Thanks for your
continued help.

Ann

 

[JLatham]

Give me a day or so and I'll work up some code for you. I've already written
some that will make sure that the same person doesn't audit the same area 2
months in a row - as I said, the piece to make sure they aren't working
'next' to one another is a bit tougher and I haven't written it yet.

What I'll do is give you a link that will provide a sample workbook with the
code in it and that explains limits on how it works and how it must be set
up. That'll let all see a possible solution, and if it needs more detailed
work, you and I can do that offline via email.

 

[JLatham]

I got something worked out that I believe works - difficult to test
completely. But with 14 names or more, I haven't seen things go wrong yet.

The code is probably a bit bloated, but does seem to work; I tend to overdo
the use of variables so things bloat up some until I go back and clean house
a bit.

Here's a link to the Excel 2003 format file:
http://www.jlathamsite.com/uploads/ChooseNewTeams_V2.xls

 

[Ann]

Thanks! I'll try it out and see how it works. I appreciate your working
with me on this. You OBVIOUSLY have much more knowledge than I do in this
area. You've been VERY HELPFUL!!

Ann

 

[JLatham]

I'm still diddling with it - I'm definitely not totally happy with what's
there now. Sadly, after uploading it, I have found that it sometimes locks
itself in a loop trying to find a solution. So I'm trying to figure out a
better way to deal with the second part of the problem and when I do, I'll
replace the file out on the site and post that it's been updated here.

 

[JLatham]

Ann,
A new version uploaded. Still not 100% perfect - but pretty good about
coming up with the solution you want.

Problem with it is that it has to work VERY hard to come up with the
solution you want when there are only 7 names to rotate through the 7
inspection areas. Even the addition of an 8th name makes it much faster at
arriving at a solution. The more 'candidates', the better the odds for a
quick solution. With 14 names available, it has consistently come up with a
'correct' solution in mere seconds after having to examine from one to
half-a-dozen team compositions to get it right.

The same link I provided before will get you the new version of the file -
you might want to rename the older copy you already have before downloading
and saving the latest one, as it uses the same filename.