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 


0
wuss (4)
9/24/2005 1:06:50 PM
excel 39879 articles. 2 followers. Follow

7 Replies
572 Views

Similar Articles

[PageSpeed] 3

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
0
petersod (12004)
9/24/2005 1:32:45 PM
"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> 


0
wuss (4)
9/24/2005 2:19:58 PM
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
> 


0
sandymann2 (1054)
9/24/2005 2:45:19 PM
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
0
billsharpe (44)
9/25/2005 12:20:56 AM
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
> 


0
wuss (4)
9/26/2005 4:36:12 AM
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 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
>>
> 
> 
> 
0
roger1272 (620)
9/26/2005 8:35:34 AM
"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 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> 


0
wuss (4)
9/26/2005 1:04:24 PM
Reply:

Similar Artilces:

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...

image list
hi, i want to list images using a list box. can i use CImageList for .gif images? anu ...

Add a drob down list box to the tool bar?
I'm working with Microsoft Visual C++ 6.0. The software I'm working on has a main window that brings up other windows when you click on various buttons on the tool bar. I need to add a drop down list box to the tool bar to select between 2 different modes. How do I go about adding a drop down list box on the tool bar? -Lee Browning Don't you mean a combo box? There are good samples in toolbar section of codeguru. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Lee ALICEngineer" <LeeALICEngineer@discussions.microsoft.com> wrote in message news:9620EAA...

Common Contacts List
Hi Outlook 2003 We have just installed a server in our work. There are 8 computers connected to it, each with Outlook. What we would really, really like, is to have just 1 Contacts List which is accessable to all users via Outlook. Preferably, this list would live on the server. Thanks - Dave. Is the server Exchange server? If not, is it configured for LDAP? If the answer to both is No, then you would need a 3rd party program to share contacts. See http://www.slipstick.com/OUTLOOK/share.asp -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Ex...

How do I capilize a list of names on excel?
i've made a really long list of names (first and last) in two columns in excel and would like to know if there's a way of capitalizing them without going through them one by one. trivial i know but would be greatly appreciated. =upper(a1) on cell c1 =upper (b1) on cell d1 for your first and last names Now copy c1 and d1 and paste it all the way down. Then select column C and D and copy edit paste special values to lose the formulas. "vrajna" <vrajna@discussions.microsoft.com> wrote in message news:29B810C4-58C5-4550-9835-40D7C36495F6@microsoft.com... > ...

grabbing emails and paste into dist list?
Hi there, Okay, here's the scenario in Outlook 2003. I get an email from someone who has included about 50 important people in the cc field. I'd like to take all those email addresses and dump them into a distribution list in one fell swoop. So far, I hit "reply to all" and selected all of those cc email addresses, but I can not paste into a distribution list. How can I do this? Thanks, Gordon ...

Comparing first and last names in two lists #2
I have two data sources that each contain about 8000 names in seperate fields for first and last name. I can put them on seperate worksheets or append one to the other. I need to make them match and find out where they don't. Messed with consolidate but not sure if that will work for more than one column at a time. FYI data set one is from a school transportation database and data set two is from the main student database from the same school district. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages dir...

Lists & Groups (the Merge) ?
Hi I've created a (Filtered) List called "Accounting"; I've created a Global Distribution Group called "Accounting." Instead of adding users manually to the "Accounting Group"; is there a way I can associate the group with the "Accounting List"? Thanks, Jason No, if you are running Exchange 2003 you could use a query-based distribution group. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Jason" <jason@thinkingman.org> wrote in message news:63e9575b.0402031111.384e4599@posting.google.co...

ignore list
I have importet some contact data into mscrm, When I want to add these contacts to a marketing list (add marketing list members / use advanced find/ add all selected members), the adding stops with an error. I have done a trace during the error (occurs everytime I want to add these contacts) which shows me the following error: [2009-08-24 11:15:36.778] Process:OUTLOOK |Thread:5884 |Category: Unmanaged.Platform |User: PlatformUser |Level: Error | Found crmId {319C876A-CC39-DC11-9F61-0030485C3892} in ignore list. Update notification will be ignored Function: CItemHelper<struct Outlook::_Co...

User-Defined List in Sales Trx Entry restrictions
Hello: GP 7.5 on SQL 2000. We use most of the fields on the User-Defined screen in Sales Transaction Entry, especially the user-defined lists. Is there any way to lock these lists out, so certain user classes cannot add items to the list? We are having to clean them out about once a month because users are mistyping items in the field instead of looking them up and they are adding junk to the list. Is this possible in Advanced Security or SOP setup somewhere? Any assistance is appreciated. Thanks! -Joe ------=_NextPart_0001_1B7F7BE9 Content-Type: text/plain Content-Transfer-Encoding: ...

Templates and distribution list membership
For creating user-accounts/mailboxes we use Templates. These are disabled used accounts with no mailbox and some predefined settings like group membership. When creating users we copy from this Template. But when you send mail to a Distribution list, the Template-user is also a member of the list, and Exchange tries to send the mail to this user. But it doesn't have a valid address and mailbox, so an error is returned for that user to one using the Distribution list. This brings up all kinds of questions. What's a good way to avoid this ? How about using this hotfix and crea...

how to delete the persons in the list in File->Open->Other User's Folders
This link (http://www.plantops.umich.edu/support/helpdocs/Outlook/manual/adv/OpenOthrUsrsFldr.pdf) shows us how to open another person's folders using File->Open->Other User's Folders. My question is how to delete the persons in the list? i.e. clear the list. I couldn't find any options to do that. please advise. thanks!! http://www.slipstick.com/outlook/mru.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without readin...

Add Smart List creator web browser capabilities to Business Essent
Our clients have to move to Advanced management to get a web interface access to Dynamivcs GP and third party dexterity files. OR we have to buy a third party application like Anyview. OR we have to create something from scratch in .Net This limitation puts us and our clients at a competitive disadvantage. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in ...

Pick from list #4
Excell has a great feature that enters data repeatedly in a column, when it has been entered once. You can also right click and select the "pick from list" feature. What I would like to do is create a data base that works the same way.Is there a way to do that? My main goal is to hide my list from veiw and not be printed with the data sheet. perhaps you want something like a data validation list. check the following link to see if it fits your requirement. http://www.contextures.com/xlDataVal01.htm -- Message posted from http://www.ExcelForum.com ...

Problem with Address Lists and filtered Contacts
I have used the Users Org Unit to hold all of my users contacts. I followed Article ID: 319213 to create address lists and created filters using keywords such as distributors, investors, etc... My users cannot see the contacts from email client. I set the permission for Authenticated user to grant read- only, list objects, list contents of list, etc... I can't get the address list to display for user. What did I do wrong? ...

Changing default lists
If I type an asterisk (*) or a number + a period (1.) and then a space, Word starts an unordered/ordered list. I have defined some lists with formats that I prefer to the default lists that Word chooses. Is there a way that I can get Word to use my lists when I type "* " or "1. "? No, the automatic application of numbering isn't that "intelligent." What you can do is create your own lists linked to styles. For single-level numbering, the easiest approach is to modify the built-in List Bullet and List Number styles, respectively. -- Stefan B...

creating a drop down list #2
I would like to create a list of drop downs for a single cell but im a novice and don't know how to do that or where to look for help.Please help Brian The simplest way: Create your list of drop down items on your worksheet eg in cells Z1 down to Z10 Then in say cell A1 from the Toolbar Data > Validation - in the allow box select List which will open anothe box Source: enter in the box =$Z$:$Z$10 and click OK Click on the arrow to the right of A1 and your drop down list will display your items for selection. You can of course create your list of items anywhere on your worksheet ...

Outlook, categories and distribution lists
Hi... Is there any software, or Outlook trick, that can make a distribution list based on a certain category? I've downloaded a trial version of Contacts2Distribute, but all it did was to make empty distribution lists... sigh I'm using Win Vista Home Premium and Outlook 2007 Norwegian edition. -- Zadig Galbaras (nick) www.tresfjording.com The question is, if you've already gone to the trouble of creating Categories, why do you need or want to use DL's? Categories are a far more reliable and robust way to group recipients than are DL's. -- Russ Valentine [MVP-O...

Membership Directory Office 2007
I need to create a club membership directory with Access 2007 and either Word 2007 or Publisher 2007. I have a database created in Access. Now what? Read this tutorial, Publisher's catalogue merge is ideal for directories. http://office.microsoft.com/en-us/publisher/HP101921831033.aspx -- Mary Sauer http://msauer.mvps.org/ "Armymom" <Armymom@discussions.microsoft.com> wrote in message news:57FCB6CA-966E-4DF4-9A2F-769050D84F12@microsoft.com... >I need to create a club membership directory with Access 2007 and either Word > 2007 or Publisher 2007. I have a databa...

Personal Distribution list receives multiple copies of internet em
Guys, this is the second time I see this happening, on different sites: a user sends an email to a distribution list he created (personal contacts); the recipients of this distribution list receive multiple copies of this email; in fact piles of them! One or two of the recipients no longer exist, which means that the sender also receives heaps of "Undeliverable" messages relating to the non existing recipients. I can't fathom why Exchange is resending again and again; how to stop it (apart from waiting for Exchange to give up); how to prevent this from rehappening? Any...

Membership Applications
There have been a number of posts recently about membership-type applications and sending emails. For some years I was an embassy warden and had to keep track of registered nationals, maintain their email addresses, capture certain statistics such as dependents, forward embassy email notices and manage a history of all correspondence, etc., etc. I solved my requirements with a hybrid Access-Outlook implementation. Outlook after all is the ideal repository and organizer for personal details and email (I had to define a few new user fields and a modified 'Contacts' data entry f...

Sort datagridview associated with a List
Hi, If I associate a DataGridView to a LIST <>, the Grid not sorted by clicking on the header. Any idea to do this? Thanks Paul, Try implementing this: This code is pinched from one of my projects, the control being sorted is a listview, it is populated by data from SQL Server but this won't affect your project as lonbg as when you click the column headings your list contains data it should work. Private Sub lstPacks_ColumnClick(ByVal sender As Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles lstPacks.ColumnClick lstPacks.List...

Creating a registration table list based on an existing table
Hi, I have an access database that contains transactional tables which I cannot alter. There is a table called invoice. This has ClientId, ClientName and ItemNumber. The ClientID is a ten digit number while the ClientName is a business name. There are no extra fields that I can use in it. I want to create a form that will enable the staff to keep track of registration. A registration is initiated when an invoice on a client is created with the itemnumber being College. Right now, people are keeping track of attendees using a spreadsheet but I want to create a table that contain...

Global Address list #6
I am not getting any changes to the global address list. I am useing Exchange 2003 and outlook 2003 in cached exchange mode. If I remove cached exchange mode the address list shows all the changes but then when I turn it back on they are gone. I have left it on for 3 days and tried to download the address book. It says it is complete, but it doesn't work. Any ideas? Eric Basler 48 hours apparently is the maximum amount of time that it can take the Offline Address Book to download to the client but this is worse case scenario, what happens if you do a manual download of the address ...

Membership
I have an application where the admin can create users using CreateUserWizard. Is there any way I can limit the number of users that the admin is able to create. Regards Chris "Chris" <sdv098@hotmail.com> wrote in message news:m6idnXckNoPH7xLWnZ2dnUVZ8gqdnZ2d@eclipse.net.uk... >I have an application where the admin can create users using >CreateUserWizard. Is there any way I can limit the number of users that the >admin is able to create. > > Regards > > Chris Yes. The Membership Provider uses stored procedures. (Assuming yo...