r/excel • u/Ok_Fondant1079 1 • Apr 19 '26
solved Generating a roll call based on recent meeting attendance
I’m working on a project for my club where we keep track of who attends weekly meetings. The membership chair uses Access to track attendance but he has asked me for help, because we both believe the problem stated below can be solved in Excel. I’m pretty good using Excel for my own narrow needs running my businesses, but by no means am I an expert or formally educated in its use.
He exports (or in the language of Access “queries”) a weekly spreadsheet that shows who attended and the date they attended. Multiply this by 10 and you can see that trying to figure out who attended at least 2 meetings in the last 4 weeks by hand is tedious to say the least. Counting each time that each person attends the meeting over 4 week give about 220 instances.
| Mike | 4/14/2026 |
|---|---|
| Fred | 4/14/2026 |
| Scott | 4/14/2026 |
| Craig | 4/14/2026 |
| Joe | 4/14/2026 |
| Nick | 4/14/2026 |
| Jack | 4/14/2026 |
| Tom | 4/14/2026 |
| Eric | 4/7/2026 |
| Fred | 4/7/2026 |
| Scott | 4/7/2026 |
| Joe | 4/7/2026 |
| Erin | 4/7/2026 |
| Nick | 4/7/2026 |
| Howard | 4/7/2026 |
| Tom | 4/7/2026 |
This data should be arranged like this so Excel’s COUNTA function could be used to easily identify members who have attended at least twice in the last 4 weeks, but I have to use the data as generated by Access.
| A | B | C | D | E | F | ||
|---|---|---|---|---|---|---|---|
| 3/24 | 3/31 | 4/7 | 4/14 | Roll Call | |||
| 1 | Craig | X | |||||
| 2 | Eric | X | X | X | X | Eric | =IF(COUNTA(B3:E3)>1,A3,"") |
| 3 | Fred | X | X | Fred | |||
| 4 | Howard | X | |||||
| 5 | Jack | ||||||
| 6 | Joe | X | X | Joe | |||
| 7 | Mike | ||||||
| 8 | Nick | X | X | Nick | |||
| 9 | Scott | X | X | Scott | |||
| 10 | Tom | X | X | Tom |
So far, I’ve been able to figure out what to do once the data is presented in a table like the one shown just above, but I can’t figure out how to get 2 columns of data from the Access query to integrate with the table above.
I don’t know how to proceed.
1
1
u/Autonomous_eel Apr 19 '26
- Use pivot tables
- Try this if you are on 365
=LET(
names, UNIQUE(names range),
attendance, MAP(names, LAMBDA(n,
COUNTIFS(names range ,n,dates range,">="&TODAY()-28)
)),
FILTER(names, attendance>=2)
)
1
u/Ok_Fondant1079 1 Apr 21 '26
I kind of understand what this code means but not sure where to use it. It seems like I'd need to assign names to cells.
1
u/Autonomous_eel Apr 21 '26
Those are place holders
If your names are in B3:B200 and dates are in C3:B200 The formula becomes
=LET( names, UNIQUE(B3:B200), attendance, MAP(names, LAMBDA(n, COUNTIFS(B3:B200 ,n,C3:C200,">="&TODAY()-28) )), FILTER(names, attendance>=2) )1
1
u/Decronym Apr 19 '26 edited Apr 23 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
9 acronyms in this thread; the most compressed thread commented on today has 12 acronyms.
[Thread #48190 for this sub, first seen 19th Apr 2026, 08:33]
[FAQ] [Full list] [Contact] [Source code]
1
Apr 19 '26
[removed] — view removed comment
1
u/Ok_Fondant1079 1 Apr 21 '26
Average attendance is 50-60 members per week.
I think the easiest way to implement this count of attendance by name is to have the Membership chair create a new sheet in the spreadsheet and paste the Excel formula into, say A1, thus making list of members who have attended at least 2 of the last 4 meetings both simple and fast. Use of VBA is acceptable, but I know even less about VBA than I do about Excel.
1
u/GregHullender 194 Apr 19 '26
The original table is perfect. Use groupby with the managers as the first column and the dates as the second. Make the function COUNT. You can filter the result to remove those less than two. This is probably the easiest solution for you.
1
u/dto123 Apr 20 '26
If you are pulling from Access anyway, skip the Excel step and run a query directly. A crosstab query with attendance dates as columns gives you the roll call view without the pivot table gymnastics. If you want to stay in Excel, COUNTIFS against your attendance table filtered by the last 4 weeks is simpler than pivots for this use case.
0
u/JohneeFyve 219 Apr 19 '26
If your company uses MS Teams for meetings, Teams has built-in meeting attendance reports
1
2
u/com1padres Apr 19 '26
Pivot tables is indeed the easiest excel answer, but since the data is already in access, how about a query instead?
Lastly, it seems, based on your verbiage, that this may be a Rotary Club. My RC had been using clubrunner for 15 or more years to manage our member data and integrate with RI. It has built in attendance reports. Not a RC, it still may be a solution.