Member count help

Occasional Visitor

Hi, 

 

So this might be a tough one to explain but I will give it a go.. 

I am using excel to highlight various key stats to my line manager based on our organisations membership stats (via a report pulled from an external database).

Every member has a unique ID which derives from a seperate membership database, each month I am downloading the membership data report and adding it to an excel sheet which then updates a pivot table giving us lots of figures. 

 

So far so simple and theres no issues with the above. 

The tricky part being -

I am wanting to create some sort of figure to tell me how many of our organisations 'members' are new, or have left/not renewed membership each year.

i.e if we have 200 members, come April when everyone is supposed to renew I want to see if the 200 members are all the same, or if I have 180 have renewed and 20 are actually new members. 

As mentioned, I have unique identifiers for all members but I am just not sure how to get the figure I need.

Note: The correct data will not actually be available until 1st April 23 when the new membership period starts, but I want to have it set up now so I am ready and can test that it works. 

 

Any ideas?

 

Thanks in advance, 

Dale

Windows 11, MS Excel (latest)

1 Reply

@DaleyT One idea would be to use one of the available lookup functions. You can lookup old member is the new list. The ones found have renewed, the ones not found have not. Then you lookup the members from the new list in the old list. The ones not found are new members.

 

And if you want to go a bit more fancy you could automate it with Power Query.