membership list

Friend of mine manages the membership list for a club, about 500 or so,
using Excel as the program.

It contains the usual; names, addresses, phone numbers, skill levels.
Presently there are five levels.

As can be expected, the list constantly changes with members moving to
different levels, coming or leaving the club. There has to be periodic
reports to the executive of the numbers in each category.

I suggested adding a couple of blank lines after each category and using the
CountA function to count the number of members in each category but I'm not
very good at Excel either.

The category beside each member is identified by A for advanced, I1, I2, I3
for intermediate levels and beg for beginner level.

Can the formula be adapted to count the number of "a" etc. in the list?

Another cell could be used to count the total membership.

How would other users approach the task?

Thanks

I wouldn't put any blank rows in the data.

I'd try to keep the list as granular as possible.

Don't put city, st, zip in one cell--use 3.

But excel has a few functions that may help:

=countif(a1:a1000,"i1")

There's also an =sumif() if you need to find add stuff based on what's in a
column.

And when you really get industrious, you can learn about pivottables.

cruncher wrote:
>
> Friend of mine manages the membership list for a club, about 500 or so,
> using Excel as the program.
>
> It contains the usual; names, addresses, phone numbers, skill levels.
> Presently there are five levels.
>
> As can be expected, the list constantly changes with members moving to
> different levels, coming or leaving the club. There has to be periodic
> reports to the executive of the numbers in each category.
>
> I suggested adding a couple of blank lines after each category and using the
> CountA function to count the number of members in each category but I'm not
> very good at Excel either.
>
> The category beside each member is identified by A for advanced, I1, I2, I3
> for intermediate levels and beg for beginner level.
>
> Can the formula be adapted to count the number of "a" etc. in the list?
>
> Another cell could be used to count the total membership.
>
> How would other users approach the task?
>
> Thanks

--

Dave Peterson

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4335557D.3AB0CEFD@verizonXSPAM.net...
>I wouldn't put any blank rows in the data.
>
> I'd try to keep the list as granular as possible.

OK

>
> Don't put city, st, zip in one cell--use 3.

I'd have to check, but I believe that's how it's organized now. I don't have
the database here.

>
> But excel has a few functions that may help:
>
> =countif(a1:a1000,"i1")

Didn't think of the countif function. Thanks for that tip.

>
> There's also an =sumif() if you need to find add stuff based on what's in
> a
> column.

Will look at this too.

>
> And when you really get industrious, you can learn about pivottables.

Maybe. :-)

I'm just playing with this stuff in a dummy datebase for now. I don't have
the real one here. Not sure just how deep I want to get into this.

But your help is appreciated and I'll pass it on.


>
> cruncher wrote:
>>
>> Friend of mine manages the membership list for a club, about 500 or so,
>> using Excel as the program.
>>
>> It contains the usual; names, addresses, phone numbers, skill levels.
>> Presently there are five levels.

<snip>

Cruncher,

As well as Dave's excellent advice you can also see the different categories
singled out from a batch of mixed entries.

You do that by hilighting all the data, then selecting Data > Filter >
AutoFilter which will put an arrow head at the top of each column. Left
clicking on the arrow head of the category column will give you a dropdown
list, Selecting the "A" will hide all other rows except the Advanced
entries.

You can then print off the shortened list or copy and paste it to another
location, (but make sure that it is not on any of the filtered rows). You
can do both filtering and copying the data at once by using Advanced Filter,
but le's go one step at a time.

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"cruncher" <wuss@unreal.net> wrote in message
news:11jajreaovilk90@corp.supernews.com...
> Friend of mine manages the membership list for a club, about 500 or so,
> using Excel as the program.
>
> It contains the usual; names, addresses, phone numbers, skill levels.
> Presently there are five levels.
>
> As can be expected, the list constantly changes with members moving to
> different levels, coming or leaving the club. There has to be periodic
> reports to the executive of the numbers in each category.
>
> I suggested adding a couple of blank lines after each category and using
> the CountA function to count the number of members in each category but
> I'm not very good at Excel either.
>
> The category beside each member is identified by A for advanced, I1, I2,
> I3 for intermediate levels and beg for beginner level.
>
> Can the formula be adapted to count the number of "a" etc. in the list?
>
> Another cell could be used to count the total membership.
>
> How would other users approach the task?
>
> Thanks
>

cruncher wrote:
> Friend of mine manages the membership list for a club, about 500 or so,
> using Excel as the program.
>
> It contains the usual; names, addresses, phone numbers, skill levels.
> Presently there are five levels.
>
> As can be expected, the list constantly changes with members moving to
> different levels, coming or leaving the club. There has to be periodic
> reports to the executive of the numbers in each category.
>
> I suggested adding a couple of blank lines after each category and using the
> CountA function to count the number of members in each category but I'm not
> very good at Excel either.
>
> The category beside each member is identified by A for advanced, I1, I2, I3
> for intermediate levels and beg for beginner level.
>
> Can the formula be adapted to count the number of "a" etc. in the list?
>
> Another cell could be used to count the total membership.
>
> How would other users approach the task?
>
> Thanks
>
>
I also manage a membership list. I'd approach the problem by first
sorting the list by category, then using Data - Subtotals - Count. You
can use the brackets that show up on the left of the row listing to get
a summary view showing only the number of members in each category.

Bill

Bill

Well, we took the suggestions and put them to work. We're impressed. Sorted
and printed wonderfully. Saves a lot of hard work.

Appreciate the help.

Something else popped up during the evening which we didn't think of when
posing the first question.

The database with the several categories of skill levels contains male and
female of course! Column F contains the letter M or F to designate that. How
would a person build a formula that would combine the data from that column
as well as the information of the category in the other column?

IOW, the Excel has to search for two criteria. Any attempts of mine to build
such a formula just upset the computer. :-)



"cruncher" <wuss@unreal.net> wrote in message
news:11jajreaovilk90@corp.supernews.com...
> Friend of mine manages the membership list for a club, about 500 or so,
> using Excel as the program.
>
> It contains the usual; names, addresses, phone numbers, skill levels.
> Presently there are five levels.
>
> As can be expected, the list constantly changes with members moving to
> different levels, coming or leaving the club. There has to be periodic
> reports to the executive of the numbers in each category.
>
> I suggested adding a couple of blank lines after each category and using
> the CountA function to count the number of members in each category but
> I'm not very good at Excel either.
>
> The category beside each member is identified by A for advanced, I1, I2,
> I3 for intermediate levels and beg for beginner level.
>
> Can the formula be adapted to count the number of "a" etc. in the list?
>
> Another cell could be used to count the total membership.
>
> How would other users approach the task?
>
> Thanks
>

Hi

One way, assuming your category to be in column E and *** in column F
=SUMPRODUCT(--($F$2:$F$1000="F"),--($E$2:$E$1000="A"))
change ranges to suit.

Regards

Roger Govier


cruncher wrote:
> Well, we took the suggestions and put them to work. We're impressed. Sorted
> and printed wonderfully. Saves a lot of hard work.
>
> Appreciate the help.
>
> Something else popped up during the evening which we didn't think of when
> posing the first question.
>
> The database with the several categories of skill levels contains male and
> female of course! Column F contains the letter M or F to designate that. How
> would a person build a formula that would combine the data from that column
> as well as the information of the category in the other column?
>
> IOW, the Excel has to search for two criteria. Any attempts of mine to build
> such a formula just upset the computer. :-)
>
>
>
> "cruncher" <wuss@unreal.net> wrote in message
> news:11jajreaovilk90@corp.supernews.com...
>
>>Friend of mine manages the membership list for a club, about 500 or so,
>>using Excel as the program.
>>
>>It contains the usual; names, addresses, phone numbers, skill levels.
>>Presently there are five levels.
>>
>>As can be expected, the list constantly changes with members moving to
>>different levels, coming or leaving the club. There has to be periodic
>>reports to the executive of the numbers in each category.
>>
>>I suggested adding a couple of blank lines after each category and using
>>the CountA function to count the number of members in each category but
>>I'm not very good at Excel either.
>>
>>The category beside each member is identified by A for advanced, I1, I2,
>>I3 for intermediate levels and beg for beginner level.
>>
>>Can the formula be adapted to count the number of "a" etc. in the list?
>>
>>Another cell could be used to count the total membership.
>>
>>How would other users approach the task?
>>
>>Thanks
>>
>
>
>

"Roger Govier" <roger@nospamtechnology4u.co.uk> wrote in message
news:4337B2D6.4010309@nospamtechnology4u.co.uk...
> Hi
>
> One way, assuming your category to be in column E and *** in column F
> =SUMPRODUCT(--($F$2:$F$1000="F"),--($E$2:$E$1000="A"))
> change ranges to suit.
>
> Regards
>
> Roger Govier

I believe you have the correct columns. I don't have the database here. I'm
only assisting and don't own it.

I will pass this on tonight and if we have time, test it. We should be able
to modify the formula if required.

Thanks

<snip>