Short Course booking problem

[Laurence Wilmer]

Simple problem (to you, I'm sure):-

Local tennis club running a 3 week short course that runs am and pm, so he
has about 100 names and other data (eg DOB, Phone no. etc) and against each
name the week booked and whether am or pm.
What's need is a way to list everyone who should be there eg on a given
afternoon.

I have suggested it needs each session numbered, but with 2 sessions a day
this means a lot more data input. Eg someone doing morning sessions for the
first week would need to add sessions 1,3,5,7,9.
OH! I guess they would each have to be in a different column - ugh! I can't
get my brain around this, there must be a simple way to do it.
Macros?

Any suggestions very welcome!
Thanks,
Laurence

 

[dvt]

Local tennis club running a 3 week short course that runs am and pm,
so he has about 100 names and other data (eg DOB, Phone no. etc) and
against each name the week booked and whether am or pm.
What's need is a way to list everyone who should be there eg on a
given afternoon.

It sounds to me like you could use an autofilter. You can filter out the
names based on the week booked and the AM/PM columns.

If you're not familiar with the AutoFilter function, it's on the Data menu
in Excel. The help function is better than any description I would write.

Dave
dvt at psu dot edu

 

[Laurence Wilmer]

It sounds to me like you could use an autofilter. You can filter out the
names based on the week booked and the AM/PM columns.

If you're not familiar with the AutoFilter function, it's on the Data menu
in Excel. The help function is better than any description I would write.

Dave
dvt at psu dot edu

Thanks, Dave, autofilter would work fine if everyone followed a simple week
1,2,3 am or pm or all day pattern, but as usual phrasing the question was my
weakness (!).

I failed to specify that a goodly number of people are only booking certain
am's, pm's or odd days, they are not all booking week 1,2,3 or am, pm, day
etc

It's dealing with the oddities that's the problem - need some way of
generating the session numbers from those who are booking to a simple
pattern, so as to only have to input manually all session numbers from those
who are erratic.

Thanks again,
Laurence

 

[dvt]

Laurence Wilmer wrote...

Thanks, Dave, autofilter would work fine if everyone followed a simple
week 1,2,3 am or pm or all day pattern, but...a goodly
number of people are only booking
certain am's, pm's or odd days, they are not all booking week 1,2,3 or
am, pm, day etc

It's dealing with the oddities that's the problem - need some way of
generating the session numbers from those who are booking to a simple
pattern, so as to only have to input manually all session numbers from
those who are erratic.

Yes, you have a tough problem. I think it's a data structure problem. That's
not a field that I'll pretend to know anything about. You might be lucky if
you find someone in the Excel groups that has solved a similar problem.

I'd suggest re-writing your original query and posting it again to another
group (i.e. excel.misc). I think people tend to ignore threads that already
have a response, so you might get a better chance of catching the proper
person's eye by starting anew. To keep from being flamed, state in your post
that you've posted a similar query in another group but you couldn't find
the answer.

I'll think about your problem a bit more, but don't count on me for
the answer. Sorry I couldn't be of more help.

Dave
dvt at psu dot edu

 

[Laurence Wilmer]

Laurence Wilmer wrote...

Yes, you have a tough problem. I think it's a data structure problem. That's
not a field that I'll pretend to know anything about. You might be lucky if
you find someone in the Excel groups that has solved a similar problem.

I'd suggest re-writing your original query and posting it again to another
group (i.e. excel.misc). I think people tend to ignore threads that already
have a response, so you might get a better chance of catching the proper
person's eye by starting anew. To keep from being flamed, state in your post
that you've posted a similar query in another group but you couldn't find
the answer.

I'll think about your problem a bit more, but don't count on me for
the answer. Sorry I couldn't be of more help.

Dave
dvt at psu dot edu

Thanks for the reply (and the thinking!).
At least that reassures me that its not a simple problem I ought to have
known the answer to!
My thoughts at the moment are options:

(1) It needs a separate table with all the sessions linked to person IDs -
but then some tricky work needed to make data input simple.
All a bit complex, Access rather than Excel

(2) A long string of columns listing all the sessions (3 weeks x 5 day week
x am/pm = 30). A macro for each of the simple cases (week 1 am, week 1 pm,
week 2 etc) to put and x in each of the correct columns, then do the odd
bods individually.

(3) I never trust macros - keep it simple and make the first column a text
statement of the days booked, then add a week column with erratic attendees
as week 0, and a column for am/pm, using am pm for day.

Then to list attendees for each session, sort the table by week, block
select and use Hide/Unhide to hide the irrelevant weeks.

Individually Hide/Unhide the week 0 people, one row at a time.

Finally Data/Autofiter the remaining data using a custom filter "contains
am" or "contains pm" to get a final list.

Not elegant, but I think that would work, wouldn't it?

Many thanks,
Laurence

 

[dvt]

(1) It needs a separate table with all the sessions linked to person
IDs - but then some tricky work needed to make data input simple.
All a bit complex, Access rather than Excel

Yeah, that solution sounds like a database problem.

(2) A long string of columns listing all the sessions (3 weeks x 5
day week x am/pm = 30). A macro for each of the simple cases (week 1
am, week 1 pm, week 2 etc) to put and x in each of the correct
columns, then do the odd bods individually.

Lots of columns. No need for a macro, you could populate all of those
columns with a formula. But it would be a bit unwieldy for viewing, I
think.

(3) I never trust macros - keep it simple and make the first column a
text statement of the days booked, then add a week column with
erratic attendees as week 0, and a column for am/pm, using am pm for
day.

Then to list attendees for each session, sort the table by week, block
select and use Hide/Unhide to hide the irrelevant weeks.

Individually Hide/Unhide the week 0 people, one row at a time.

Finally Data/Autofiter the remaining data using a custom filter
"contains am" or "contains pm" to get a final list.

Not elegant, but I think that would work, wouldn't it?

I agree. I thought of one other option, and the same description would
apply. Have one column titled date/week, another titled AM/PM. The AM/PM
column is self-explanatory. In the date/week column, enter a code for the
full-week participants. F'rinstance, you might use A, B, and C for weeks 1,
2, and 3. For "erratic attendees," put a date in that column. Now you can
custom autofilter on that single column.

Downside to this solution: if an erratic attendee schedules more than one
day, you will need to enter a corresponding number of rows in your table for
that attendee. A sample table follows, where Joe and Dick have each signed
up for a full week and Jane has signed up for two days.

Name Date/Week AM/PM
Joe A AM
Jane 21 AM
Jane 22 AM
Dick B PM

Assume the 21st is part of Week A. Sort the table to find Date/Week=A or
Date/Week=21 and you'll find Joe and Jane, the two attendees that are
supposed to be there on the 21st.

Dave
dvt at psu dot edu