It is common for challenge administrators to run both property and space challenges for one program. Because both property and space participants enroll through the same challenge website, the “Additional Users” spreadsheet from the Challenge Admin Dashboard only considers the challenge currently being viewed when calculating the number of users needed to complete enrollment. This means there might be people in the file that did not enroll in the property challenge but may have enrolled in the space challenge and visa versa.
This guide will show you how to use conditional formatting in Excel to 1) identify the users who created an account (Step 1) but have not enrolled in either challenge (Step 2 and/or Step 3) and 2) create an email list of participants needing to complete the sign-up process.
1. Download the “Additional Users” file for both the property and space challenges.
2. In one of the files, select ALL of the rows except for the heading.
3. Copy and Paste the selected rows into the first empty cell in Column A of the other “Additional Users” file.
4. Select Column C, which contains all of the user emails, and click Conditional Formatting in the “Home” formatting tab. Depending on your version of Excel, the tool may be located elsewhere in the application.
5. Select “Highlight Cells Rules” then “Duplicate Values…”.
6. Click OK.
7. With Column C still selected, sort the Emails in Ascending order.
8. In the “Sort Warning” pop-up, make sure “Expand the Selection” is selected. Click Sort.
The users now highlighted have not completed the enrollment process by not completing either Step 2 or Step 3. These users can be targeted by an email encouraging them to properly enroll in a challenge of their choosing.
Once you highlighted a list of users who have not enrolled in either challenge, you must separate these users from the original file and de-duplicate the list for email purposes.
1. To filter the list to only users not enrolled in either the Property or Space Challenges, click the filter button while cell C1 is selected.
2. Click the dropdown arrow in cell C1.
3. Click “By Color”, then “Cell Color”, then select the faded red color.
4. Select all of the cells using the select all button. Copy those cells.
5. Add a new sheet to the Workbook and paste the selected cells into that sheet.
6. With the cells still selected, click on the “Remove Duplicates” button in the Data tab. Depending on your version of Excel, this tool may be located somewhere else in the application.
7. Click “Remove Duplicates”.
This is now a list of users who are not participating in either the Property or Space Challenge.
If you have any questions or would like a walkthrough of the CAD or using conditional formatting, contact firstname.lastname@example.org and we would be happy to assist you.