Count across other files

Copper Contributor
I have 12 spreadsheets for gym membership, one for each month and each one contains a table showing whether a member is active or not in that month. Example below for September.
 
Member IDActive
1Y
2N
3Y
4N
5
N
6N
 
The next month, October the table might look like this:
 
Member IDActive
1N
2N
3Y
4Y
5
Y
6N
 
That would mean that MemberID 1,3,4 and 5 would have been active, ie a count of 4.
 
What's the best way of creating a count for "The number of members active in the last 6 months"? So in October I would need to find this count using data from the Oct, Sep, Aug, Jul, Jun and May files.
 
Thanks
15 Replies

@chrishall166 

 

 

online sql:

 

select count(distinct(f03)),group_concat(distinct(f03)) from consolidateSheet where f04 like 'Y'

 

Screenshot_2024-06-14-08-10-44-008_com.mmbox.xbrowser.pro.jpg

@chrishall166 

 

Please see the attached file for the solution. 

@chrishall166 

May I clarify what are you going to show at the beginning of the year (in Jan, Feb, Mar, etc) where you have no 6-months back history?

As a comment, from data modelling point of view it's always better not to split workbook on monthly sheets but keep in one sheet log table for all members and all periods, creating all kind of reports based on it.

Finally, which Excel platform/version do you use?

Hi Sergei, thanks for the response.

This is all to do with a charity providing food for those in need, not a gym as I put in my first post. As we have received funding from a council they are asking for activity stats and reporting these stats is where the headache is.

We only have 3 months of data so we have to provide that in lieu of 6 months. The people engaged by the charity who deal with the clients may have little or no experience of Excel so I don't want to send them a huge file which they may break!

So I send them a file for each months which provides some monthly stats. I then manually transfer a small table to the new sheets for other cumulative stats.

As I'm writing this in response I've just thought of a way to do what I need to do so I will go away and have a play. If I hit a dead end I may be back for more ideas!

Thanks

Chris

@chrishall166 

Hi Chris,

So, here is half administrative half technical task. From administrative point of view it's desirable what clients provide you information in exactly the same format for all them. As simple as possible taking into account they have no experience in Excel, but have all necessary information. I guess the key point period shall be mentioned.

Technically it's better not to add each such report into separate sheet but keep log in one master sheet appending report one to another. Perhaps some problem will be to add period for each record, but that's solvable. Hope these activities will continue for years and one master log will be more reliable solution.

Having master log you may generate any report for your sponsors or for your own needs. Using formulae or PivotTable.

All above could be done fully automatically or half-automatically, depends on platform you use. 

Anyway, it's better to have samples of actual report with sensitive information removed.

Let me suggest that--if it's something you're in a position to do--you create a single database rather than separate monthly ones. Just add a column to represent the month. Once you've done that, a Pivot Table will do all the work for you. See the attached.

 

(Note: to make it easier to sum the activities, I used a 1 instead of "Y" to indicate activity. But you could also let the Pivot Table just count by entering only the "Y" when a person was active in a given month; by omission, the blank would mean 'N" and the blank would not be counted. If you wanted to get more granular (and thereby more substantive or meaningful), you could record every date the person was active and count those by month)

 

Thanks all for your help so far!

 

I've had additional issues with @sumproduct not working on other laptops, I think due to different versions so have rewritten the spreadsheet to make it more reliable.

 

Issue still remains with how to count activity / attendance over the last 6 months period.

 

I've created an additional table below showing activity be members over the months. A '1' indicates that the member has attended at least once in the month.

 

The specific question I need to answer is "How many members have been actively attending the pantry in the last 6 months?"

 

I don't need to count how many visits per member, just if a member has attended any time within the last 6 months then that will count as a "1".

 

So for March the answer would be "2".

 

For April the answer would be "4"

 

For May the answer would be "6"

 

The awkward part is the table will need to cover one year and so will need to include the previous 6 months data, which I can manually copy over from the previous years file.

 

Thanks!

 

          Attendance
Member NumberAprMayJunJulAugSepOctNovDecJanFebMarAprMayJunJulAugSepOctNovDec
1000000000001110000000
2

0

0000

0

000000110000000
3000000000000100000000
4000000000001010000000
5000000000000010000000
6000000000000010000000

@chrishall166 

 

I don't see a connection between the data you show in that matrix and the question you ask.

 

Is it possible for you to post an actual spreadsheet rather than just text in a posting? If you can't attach it directly as I did in my earlier response to you, then post it on OneDrive or GoogleDrive and paste a link here that grants access.

@mathetes 

 

Sticking to the table, let's say I'm interested in calculating which members have been active in April and the preceding 5 months. I'm not interested in how many for each member just if a member has been active at all.

 

So in the table for the month of April:

Member 1 has been active in the last 6 months

Member 2 has been active in the last 6 months

Member 3 has been active in the last 6 months

Member 4 has been active in the last 6 months

Member 5 has not been active in the last 6 months

Member 6 has not been active in the last 6 months

 

The answer is 4.

 

How can I calculate that?

 

Thanks

Previously you mentioned that excel workbook has the month wise sheet but it seems you have data in the single sheet. so you want to count in single sheet?

@chrishall166 

select * from consolidateSheet where f04 like 'Y';

select f03,group_concat(f02) from consolidateSheet where f04 like 'Y' group by f03 having(count(f03)>2);

 

f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 f22 f23 f24 f25

Count Member2.xlsxJan1Y---------------------
Count Member2.xlsxJan3Y---------------------
Count Member2.xlsxFeb3Y---------------------
Count Member2.xlsxFeb4Y---------------------
Count Member2.xlsxFeb5Y---------------------
Count Member2.xlsxMar3Y---------------------
Count Member2.xlsxMar4Y---------------------
Count Member2.xlsxMar5Y---------------------

f03 group_concat(f02)

3Jan,Feb,Mar

@chrishall166 

So in the table for the month of April:

Member 1 has been active in the last 6 months

Member 2 has been active in the last 6 months

Member 3 has been active in the last 6 months

Member 4 has been active in the last 6 months

Member 5 has not been active in the last 6 months

Member 6 has not been active in the last 6 months

 

The answer is 4.

 

 

But how does that set of descriptions of each member's activity (or lack thereof) connect with that prior table? This one!

mathetes_0-1718935612416.png

 

And that still isn't an actual spreadsheet.

 

I think we're talking past each other; not sure why. I am also on vacation now so not checking except sporadically. But I'd really like to get a clear description of how you store your data and the questions that need answering, and have the former in the form of a spreadsheet, on OneDrive or GoogleDrive.

Thanks all for the responses.

This 'system' is going to be used by people with limited experience of spreadheet programs and limited experience of directory structures so I've gone away from a monthly file system to having everything in one file. I want to prepare that file for the current year and send it out to the person who's typing the daily attendance data in.

The file also contains personal data so I can't share it here or link from OneDrive. There's also a lot of other things going on in the file so I can't pick and choose tabs to share as it will just break. This is why I'm creating example tables on here so people can help me grasp the concept which I can take away and apply to the real file.

Thanks.

@chrishall166 

 

I totally understand why you can't post the actual.

 

I don't understand why you seem to be resisting posting a mockup of the way you're actually storing data, especially given the fact that more than one of us have replied with an attempt at showing a possible solution.

 

You could take one of those and modify it. The samples you've given don't fully make sense--certainly aren't consistent from one posting to another. So give us a representative mockup to work with, representative in that it is a relatively accurate re-creation of your reality, without any identifiable names, etc.

Hi @methetes, I appreciate the help you and other board members have given me. However due to the complexity of the questions I need to answer on statistics coupled with the fact I need to make it simple for anyone to enter data the spreadsheet it's very difficult to recreate an accurate mockup on a medium such as this discussion board. And it's not been a linear progression. I create answers for Statistic A, B, C but then to create a solution for Statistic D I have to go back and modify A/B/C.

The question on the table I posted on the 19th is the last piece of the puzzle and the answers have allowed me to grasp the concepts and create a working solution.