Weekly meeting tracker

[Rob_010101]

Hello,

Each week, my whole company attends a meeting. Before each meeting, an email is sent out with a Microsoft Forms link which the whole company must complete to state they've acknowledged and understand the briefing. This is because some employees who work nights can't attend a daytime meeting. I need to create a system where I can track who has completed the MS form each week, using the data download from MS forms each week

MS forms data example

Amaro Safety Stand Down 11_04_2024.xlsx
	A	B	C	D	E	F	G
1	Id	Start time	Completion time	Full Name	Is your role office or site based?	What knowledge have you gained from the safety bulletins?	I have read and understood the meeting
2	1	4/10/24 21:51:34	4/10/24 21:52:35	Person 1	Office	Anonymised	Yes
3	2	4/11/24 9:26:01	4/11/24 9:27:05	Person 2	Office	Anonymised	Yes
4	3	4/11/24 9:33:44	4/11/24 9:36:15	Person 3	Office	Anonymised	Yes
5	4	4/11/24 9:40:34	4/11/24 9:42:36	Person 4	Office	Anonymised	Yes
6	5	4/11/24 9:39:43	4/11/24 9:43:25	Person 5	Office	Anonymised	Yes
7	6	4/11/24 9:44:07	4/11/24 9:44:48	Person 6	Office	Anonymised	Yes
8	7	4/11/24 9:56:21	4/11/24 9:58:55	Person 7	Site	Anonymised	Yes
9	8	4/11/24 10:02:23	4/11/24 10:07:55	Person 8	Office	Anonymised	Yes
10	9	4/11/24 10:17:16	4/11/24 10:19:15	Person 9	Office	Anonymised	Yes
11	10	4/11/24 10:31:44	4/11/24 10:35:20	Person 10	Office	Anonymised	Yes
12	11	4/11/24 10:40:08	4/11/24 10:45:47	Person 11	Office	Anonymised	Yes
13	12	4/11/24 12:17:01	4/11/24 12:19:02	Person 12	Office	Anonymised	Yes
14	13	4/11/24 12:32:05	4/11/24 12:35:12	Person 13	Site	Anonymised	Yes
15	14	4/11/24 12:39:24	4/11/24 12:41:46	Person 14	Site	Anonymised	Yes
16	15	4/11/24 13:19:15	4/11/24 13:20:20	Person 15	Office	Anonymised	Yes
17	16	4/11/24 14:19:13	4/11/24 14:24:08	Person 16	Office	Anonymised	Yes
18	17	4/11/24 15:02:39	4/11/24 15:09:30	Person 17	Site	Anonymised	Yes
19	18	4/11/24 15:51:03	4/11/24 15:51:38	Person 18	Office	Anonymised	Yes
20	19	4/11/24 9:48:34	4/11/24 15:53:36	Person 19	Office	Anonymised	Yes
21	20	4/11/24 15:52:48	4/11/24 15:54:11	Person 20	Office	Anonymised	Yes
22	21	4/11/24 15:50:47	4/11/24 15:54:16	Person 21	Office	Anonymised	Yes
23	22	4/11/24 15:50:32	4/11/24 15:54:36	Person 22	Office	Anonymised	Yes
24	23	4/11/24 15:53:39	4/11/24 15:54:42	Person 23	Site	Anonymised	Yes
25	24	4/11/24 15:50:36	4/11/24 15:54:48	Person 24	Office	Anonymised	Yes
26	25	4/11/24 15:52:42	4/11/24 15:55:13	Person 25	Office	Anonymised	Yes
27	26	4/11/24 15:55:14	4/11/24 15:56:27	Person 26	Site	Anonymised	Yes
28	27	4/11/24 15:55:11	4/11/24 15:57:52	Person 27	Site	Anonymised	Yes
29	28	4/11/24 15:56:25	4/11/24 15:58:57	Person 28	Office	Anonymised	Yes
30	29	4/11/24 15:58:18	4/11/24 15:59:10	Person 29	Office	Anonymised	Yes
31	30	4/11/24 16:00:13	4/11/24 16:02:22	Person 30	Office	Anonymised	Yes
32	31	4/11/24 15:57:00	4/11/24 16:02:26	Person 31	Site	Anonymised	Yes
33	32	4/11/24 15:53:09	4/11/24 16:05:34	Person 32	Office	Anonymised	Yes
34	33	4/11/24 12:50:00	4/11/24 16:05:39	Person 33	Site	Anonymised	Yes
35	34	4/11/24 17:36:10	4/11/24 17:40:15	Person 34	Office	Anonymised	Yes
36	35	4/11/24 18:13:53	4/11/24 18:16:33	Person 35	Site	Anonymised	Yes
37	36	4/11/24 18:20:31	4/11/24 18:26:29	Person 36	Office	Anonymised	Yes
38	37	4/11/24 19:43:55	4/11/24 19:45:39	Person 37	Site	Anonymised	Yes
39	38	4/11/24 23:25:42	4/11/24 23:47:11	Person 38	Site	Anonymised	Yes
40	39	4/12/24 0:37:56	4/12/24 0:40:34	Person 39	Site	Anonymised	Yes
41	40	4/12/24 0:45:12	4/12/24 0:49:18	Person 40	Site	Anonymised	Yes
42	41	4/12/24 0:49:57	4/12/24 0:52:54	Person 41	Site	Anonymised	Yes
43	42	4/12/24 1:48:38	4/12/24 2:00:44	Person 42	Site	Anonymised	Yes
44	43	4/12/24 5:35:16	4/12/24 5:38:11	Person 43	Site	Anonymised	Yes
45	44	4/12/24 6:10:22	4/12/24 6:13:22	Person 44	Site	Anonymised	Yes
46	45	4/12/24 11:21:40	4/12/24 11:25:50	Person 45	Office	Anonymised	Yes
47	46	4/12/24 11:20:41	4/12/24 11:34:31	Person 46	Office	Anonymised	Yes
48	47	4/12/24 12:12:10	4/12/24 12:15:08	Person 47	Site	Anonymised	Yes
49	48	4/12/24 15:34:43	4/12/24 15:43:40	Person 48	Office	Anonymised	Yes
50	49	4/12/24 16:26:56	4/12/24 16:30:09	Person 49	Site	Anonymised	Yes
51	50	4/12/24 17:44:04	4/12/24 17:49:29	Person 50	Site	Anonymised	Yes
52	51	4/13/24 14:37:20	4/13/24 14:42:29	Person 51	Site	Anonymised	Yes
53	52	4/15/24 8:45:33	4/15/24 9:00:28	Person 52	Office	Anonymised	Yes
54	53	4/16/24 12:01:21	4/16/24 12:07:38	Person 53	Office	Anonymised	Yes
Forms Data

I'd like the tracker to look something like this, with the date along the top being the date of the meeting and then Yes or No being whether they completed the form:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Employee Name	11/04/2024	18/04/2024	25/04/2024	02/05/2024	09/05/2024	16/05/2024	23/05/2024	30/05/2024	06/06/2024	13/06/2024	20/06/2024	27/06/2024	04/07/2024	11/07/2024	18/07/2024	25/07/2024	01/08/2024	08/08/2024	15/08/2024	22/08/2024
2	Person 1	Yes	No
3	Person 2	No	No
4	Person 3	Yes	Yes
5	Person 4	Yes	No
6	Person 5	No	Yes
7	Person 6	Yes	Yes
8	Person 7	Yes	Yes
9	Person 8	Yes	Yes
10	Person 9	Yes	Yes
11	Person 10	Yes	Yes
12	Person 11	Yes	Yes
13	Person 12	Yes	Yes
14	Person 13	Yes	Yes
15	Person 14	Yes	Yes
16	Person 15	Yes	Yes
17	Person 16	Yes	Yes
18	Person 17	Yes	No
19	Person 18	Yes	Yes
20	Person 19	Yes	Yes
21	Person 20	Yes	Yes
22	Person 21	Yes	Yes
23	Person 22	Yes	No
24	Person 23	Yes	Yes
25	Person 24	Yes	Yes
26	Person 25	Yes	Yes
27	Person 26	Yes	Yes
28	Person 27	Yes	No
29	Person 28	Yes	Yes
30	Person 29	Yes	Yes
31	Person 30	No	Yes
32	Person 31	Yes	Yes
33	Person 32	No	No
34	Person 33	No	No
35	Person 34	No	Yes
36	Person 35	Yes	No
37	Person 36	Yes	Yes
38	Person 37	Yes	Yes
39	Person 38	Yes	Yes
40	Person 39	Yes	No
41	Person 40	Yes	Yes
42	Person 41	Yes	Yes
43	Person 42	Yes	Yes
44	Person 43	Yes	Yes
45	Person 44	Yes	Yes
46	Person 45	Yes	Yes
47	Person 46	Yes	Yes
48	Person 47	Yes	Yes
49	Person 48	No	No
50	Person 49	No	No
51	Person 50	Yes	Yes
Sheet1

Employees are required to type their name into the form, so it may be different to how we have it recorded, i.e. Christopher may be typed as Chris - and also a lot of them do this on their phone so spelling mistakes sometimes happen. The majority will be ok but I am guessing this presents the need to introduce some sort of manual check where a name doesn't match. This may or may not be relevant but thought i'd include it anyway.

Happy with either a formula, or a VBA solution, whichever is more suitable.

Any help greatly appreciated.

Many Thanks

 

[StevenE06]

Couple questions

1) Will the ID ever appear with multiple dates for meetings attended?
2) Is it possible for you to have the ID on your consolidation sheet? or can it be only the persons name?
(If you can have ID on the consolidation sheet that removes any issues of names varying and/or typos)

 

[Rob_010101]

Couple questions

1) Will the ID ever appear with multiple dates for meetings attended?
2) Is it possible for you to have the ID on your consolidation sheet? or can it be only the persons name?
(If you can have ID on the consolidation sheet that removes any issues of names varying and/or typos)

1) Start and completion time i would imagine are when they start and complete the form. Meeting is held each Thursday, deadline to complete the form is following Sunday
2) I might remove the problem with the person's name by using a drop-down names list on the form

 

[StevenE06]

1) Start and completion time i would imagine are when they start and complete the form. Meeting is held each Thursday, deadline to complete the form is following Sunday
2) I might remove the problem with the person's name by using a drop-down names list on the form

So for question 1

I assumed the ID column was an employee ID #
In the example you have ID #1 on the 4/10 meeting but not on any other meetings.

Is the ID # an employee ID and if so, will same employee ID# show on multiple meeting dates

 

[Rob_010101]

So for question 1

I assumed the ID column was an employee ID #
In the example you have ID #1 on the 4/10 meeting but not on any other meetings.

Is the ID # an employee ID and if so, will same employee ID# show on multiple meeting dates

Ah, sorry. ID is just random. I think it's assigned in the order the person completes the form, i.e the 10th person to complete it is 10 and so on. Column C seems to be in time order.
I think in 4/10 case, the person who created the form released it in the evening and signed it straight away. Others picked it up the following morning and so on. Where I put person 1,2,3 etc, it's mere coincidence that the anonymisation of the data lined up with the ID.

 

[Rob_010101]

Can anyone help?

 

[JerrExcel]

Hi there,
If all of the names are standardized, then the solution would rather simpler and I do not suggest VBA macro for this unless there are multi-complicated criteria for matching names.

Here is my suggestion:
Step 1: Create excel with sheets "Records", and dates of meeting like "20240411", "20240418"...etc

Step 2: Always copy and paste the entire MS forms data into designated sheet. For this case, your example above, copy into the Sheet named "20240411"

Step 3: In the sheet "Records", use formula to COUNTIF employee name inside the dated MS forms data (I assume Column D will always be the Employee name in your MS forms data)

=IFERROR(IF(COUNTIF(INDIRECT("'"&TEXT(B$1,"YYYYMMDD")&"'!D:D"),$A2)>0,"Yes","No"),"")

• Iferror to exclude error to calculate those sheets yet to be created (future meetings)
• If condition: show "Yes" for countif > 0, meaning the employee's name existing in the MS form data
• Use INDIRECT to quote sheet name from the top row of the table: (in this case, the date need to be format into YYYYMMDD, because sheet name cannot be saved as "DD/MM/YYYY")
• 
• Then just drag the formula to the whole table.
• You will just need to create new sheet, rename sheet, copy paste MS form data for every week.

20240423 Tracker.xlsx
	A	B	C	D	E	F	G	H	I
1	Employee Name	11/4/2024	18/4/2024	25/4/2024	2/5/2024	9/5/2024	16/5/2024	23/5/2024	30/5/2024
2	Person 1	Yes	Yes
3	Person 2	Yes	Yes
4	Person 3	Yes	Yes
5	Person 4	Yes	Yes
6	Person 5	Yes	Yes
7	Person 6	Yes	Yes
8	Person 7	Yes	Yes
9	Person 8	Yes	Yes
10	Person 9	Yes	Yes
11	Person 10	Yes	Yes
12	Person 50	Yes	Yes
13	Person 51	Yes	Yes
14	Person 52	Yes	Yes
15	Person 53	Yes	Yes
16	Person 54	No	No
17	Person 55	No	No
18	Person 56	No	No
19	Person 57	No	No
20	Person 58	No	No
21	Person 59	No	No
22	Person 60	No	No
Records

Cell Formulas
Range	Formula
C1:I1	C1	=B1+7
B2:I22	B2	=IFERROR(IF(COUNTIF(INDIRECT("'"&TEXT(B$1,"YYYYMMDD")&"'!D:D"),$A2)>0,"Yes","No"),"")

Caution: Formula consist of INDIRECT will cause excel consume more time to calculate. If there are multiple conditions or big data files of raw data (like 10,000 of employee), excel will need time to load.
Thanks!