Using Excel for organising a seminar

[Fiona]

Hi,

I have been told about Newsgroups and how they might be
able to help me with this problem.

I am organising a seminar for approx 150 staff. Staff
come from different Depts (Finance, Marketing, Personnel,
IT, Training); each member of staff has a Grade (1-5);
staff come from 1 of 4 locations (Area1, Area2, Area3 &
Area4); and there 74 female and 76 male staff. I need to
organise these staff into 6 Groups so that each Group
contains at least 20 and no more than 30 staff, and each
Group must contain at least 3 staff from each Dept,
Location, Grade & Sex.

At the moment I manually work it out but when staff cancel
or transfer it means that I have to re-jig the whole
format to ensure that my variables are correct. Is it
possible to automate a routine in Excel to help me manage
this - plenty of more cancellations are expected?

Grateful for any views and suggestions.

Fiona

 

[Guest]

Yes, Fiona, your problem can easily be solved in Excel. By using a well
organized spreadsheet with ample formulas for counting and sorting and
filtering, you should be able to generate a PivotTable or other arrangement
to rapidly respond to cancellations or additions. Organize the columns by
criteria (department, age, sex, etc) and place one individual on each row.
Set up a second worksheet in the same workbook for counting by any of your
criteria and/or a pivottable to summarize the information.

Steve in Ohio

 

[Fiona]

Thank you Steve for your reply.
I had not considered pivot tables - good idea there.
I already have data laid out in column format so with a
bit of research and a bit of luck I will attempt a pivot
table with counting formulas. I am enjoying the challenge
of it all. Thanks for the tips.

Regards

Fiona

 

[Fiona]

Hi Steve,

Keen as I am, I have been working hard for the last 2
hours and have managed to produce a pivot table from my
data.
Pivot tables, while providing excellent summarising of
data, seem to be a manual system.
For example, if Employee1 has cancelled he is removed from
the list, say he was in Group1. As each Group requires at
least 20 staff, 10 males and females, then this reduces
the required no of males from 10 to 9. If I select
another male from another Group (say Group2) to accomodate
for the loss, this then reduces the Grades in Group2 to
below required level. The whole organisation process
across each Group snowballs if you see what I mean.
Maybe, using pivot tables, there is a way to
manage/automate the variables. Can you help?

Very much appreciated

Fiona