Objective: Create list of yearly attendance, split into “new” and “returning” (from last year). Lists are split into quarterly reports (so that if a student comes in each quarter, they will only appear in the first quarter’s report). Year begins in late August or early September, depending on how you would like to set up your lists. For large lists, here is an efficient method of gathering the data.
On RSVP page (admin/shluchim-end) use the export option on the top right. Makes no difference if from “upcoming events” page or “completed events” page.
Open downloaded file. Should automatically open in Excel.
Select all columns that are not applicable and delete them. Only keep date of event, first name, last name, email, and phone.
Select all data (top left of Excel).
>Sort & Filter
>Custom Sort.
Choose date column and newest to oldest.
After sorting by date, delete all rows of the non-relevant dates.
All names that came to multiple events, now appear multiple times in the list. To remove duplicate rows:
Select all data (top left of Excel).
>Data.
>Remove duplicates
Only select email column for the duplicate removal feature. (Email is best, since many first names and last names are similar, and many student’s don’t put phone info in).
(If you suspect that some students came but haven’t RSVP’s but they may have created a student account, follow this step.)
Go to Students page in COC website. Then export CSV.
Open file (should automatically open in Excel).
Delete all irrelevant columns, just as in previous step.
We need to delete the duplicates – many names who created a student profile, also are in the RSVP list we added earlier.
Delete duplicates by selecting all data in Excel (top left).
>Data
>Remove duplicates
Select email column when it asks which column.
This refers to quarters’ of that specific year (not applicable to QTR 1 which starts in August-September).
We don’t want names that appeared on quarterly lists from this year already to appear in the list again.
Be sure to add their emails as well to the list.
Make sure that the added names now are distinct from the current list, as we will need to delete these soon.
Select only email column.
>Conditional formatting
>Highlight cell rules
>Duplicates.
(If you’re using Google sheets: see here – but change A’s in formula to C’s for email column.)
Then to remove the highlighted emails along with their names:
Select data ONLY FROM CURRENT LIST – NOT PREVIOUS QTRs.
>Sort & Filter
>Custom sort
Sort by email column, cell color. Delete all highlighted cells. NOTE: They may all be on bottom with a big space between them and the unhighlighted list on top (or vice-versa).
Then to remove the highlighted emails along with their names:
Select data ONLY FROM CURRENT LIST – NOT PREVIOUS QTRs.
>Sort & Filter
>Custom sort
Sort by email column, cell color.
DELETE all highlighted cells. NOTE: They may all be on bottom with a big space between them and the unhighlighted list on top (or vice-versa).
Now that we are done using them for comparison purposes, we can delete them. NOTE: There is a chance they were all already deleted when deleting all highlighted duplicate cells in previous step. This is okay.
Paste names and emails from previous year’s lists to the bottom of this list.
Make sure it’s distinct from current list so that we can delete it later.
Be sure to include the email in appropriate column just like the current list.
Select only email column.
>Conditional formatting
>Highlight cell rules
>Duplicates.
(If you’re using Google sheets: see here – but change A’s in formula to C’s for email column.)
Select data ONLY FROM CURRENT LIST – NOT PREVIOUS YEARS.
>Sort & Filter
>Custom sort
Select email column, and sort by font color.
Separate the highlighted ones from the unhighlighted.
NOTE: You must separate them besides for them being highlighted since they won’t be highlighted anymore after the next step. Therefore I recommend separating them into a separate sheet altogether or making a space between the highlighted and unhighlighted.
We are done using that. Just select and delete.
Unfortunately, Excel does not yet have a feature to identify non Jewish names, so this is easiest done manually. (V-Lookup function can also help with this, but is probably not worth the effort.)
Add to a Horn folder in your Google Drive.
Name it based on the quarter and year.
Will be used for future quarters and for next year’s “returning” list.