membership list

[cruncher]

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]

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]

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.


<snip>

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Bill Sharpe]

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

 

[cruncher]

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.

 

[Roger Govier]

Hi

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

Regards

Roger Govier

 

[cruncher]

Hi

One way, assuming your category to be in column E and sex 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>