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

Mounika

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!

Paul Newcome

Do you have the challenge start and end dates on the Registration sheet?

Mounika

Hi @Paul Newcome No I don't, but can I create a auto fill columns with dates as soon as the data is being filled into form, if I can work out?

Mounika

Hi,

Can someone please help me find a solution for my questions? I have been trying in various ways but found no possibilities, I would appreciate your help. Thank you!

Joe Goetschel

Hi @Mounika, I have several ideas to help you with this request. I think it might be best to talk them all out if you're available.

Options:

1. Helper columns hidden on form, counting helper columns, formulas, etc
2. Make a folder set separate for each challenge, and link them all together with reports and dashboards.
3. Make additional sheets, use automation to move rows and make metric sheets etc.

Like I said several options I think might help you achieve your end goal.

Mounika

Hi @Joe Goetschel

Thank you so much for responding!

I've created a hidden column called "Challenge term" in a form, so it will set to default when every new challenge period begins (Example: for 1st challenge: Challenge-1, 2024: for 2nd challenge: Challenge-2, 2024;). Based on this column is it possible to compare the list of emails in registration sheet with emails in challenge sheet (the emails that were added in challenge-2, 2024). I have tried creating a reference in challenge sheet but was not able to find a right formula 😐.

And can you please elaborate on the possibilities that you have shared? That would be very helpful. Thank you so much!

Joe Goetschel

Here is what I would do.

Make another column (Flag or check box) Make it a formula column using this formula.

=IF(CountIF( {Reference another sheet},Email column on current sheet@row)>0,1,0)

This will check the box and let you know it was on the other sheet etc.

Mounika

@Joe Goetschel

I also need a condition based on Challenge term (a default dropdown, and a hidden field in the form, which will be changed for every challenge). Because, the email will be present if the person has completed has first challenge, during the second challenge, I would like to use this "Challenge term" column as a condition in the formula, to compare the emails.

Joe Goetschel

@Mounika

You can then swap out the Countif to a Countifs and add the additional conditions.

=IF(CountIF({Reference another sheet email column},Email column on current sheet@row,{Reference another sheet Challenge term},"Challenge term name")>0,1,0)

https://help.smartsheet.com/function/countifs?frame=0&nav=1