Automatic Comparision and automating the list of emails from two sheets into a third sheet.


Hi all,

I have Registration sheet, which captures the email address. After registration, the individual gets email to participate in the challenge, with link to other Challenge form (and data will be captured into another sheet "Challenge Sheet"). Based on this email address column I have linked both the sheets, and as soon as participant completes the challenge form, using the same email address used in Registration form (as email address is the only common field in both the forms), the registration information captured into Registration sheet will be linked to Challenge Sheet.

So once the person register, they will get link to challenge form, quarterly meaning each individual who register is expected to complete the challenge four times in a year. The challenge will be active for 3 months, and in the same form (challenge form), the questions will be edited as soon the new challenge begins. This means the same participants information's is captured four times a year into same sheet (Challenge Sheet) if the individual completes the challenge.

Now, after 45 days of challenge begins and also few days before the challenge completes, I wanted to send a reminder email to individuals who have not completed the challenge. Is it possible to compare the Emails in Registration sheet with the ones in Challenge sheet (but in challenge sheet it should be based on dates, for e.g. From January 1 to February 15, because, the same person, might have completed the previous challenge held in October to December) and populate who have not completed the registration? I can create the date captured field within the sheet if needed.

Simply, I need to know, for that particular challenge who did not complete the challenge, based on registration sheet emails (as challenge will be sent to all registered emails). Please help me find a solution for this, Thanks in advance!