Stumped in Excel!
May 3, 2021 5:45 AM Subscribe
Excel project at work involving thousands of employees. Help needed!
Tab 1 has the email addresses of employees who were emailed, asking them to complete a task (named range: EMAIL-SENT)
Tab 2 has email addresses of all employees who have completed the task thus far (named range: TASK-COMPLETE)
I need to follow up with those who have not completed the task.
So- I need Tab 3 to show me the email addresses of everyone in Tab 1 who is not on Tab 2, so I can send them a follow-up reminder.
How can I get Tab 3 to automatically display the desired results by comparing Tab 1 and Tab 2 in this manner? Is there a macro or formula that will do this? (I am imagining a filter output, or some kind of ongoing query against Tab 1 and Tab 2 to deliver that result?)
Thanks in advance!!
Tab 1 has the email addresses of employees who were emailed, asking them to complete a task (named range: EMAIL-SENT)
Tab 2 has email addresses of all employees who have completed the task thus far (named range: TASK-COMPLETE)
I need to follow up with those who have not completed the task.
So- I need Tab 3 to show me the email addresses of everyone in Tab 1 who is not on Tab 2, so I can send them a follow-up reminder.
How can I get Tab 3 to automatically display the desired results by comparing Tab 1 and Tab 2 in this manner? Is there a macro or formula that will do this? (I am imagining a filter output, or some kind of ongoing query against Tab 1 and Tab 2 to deliver that result?)
Thanks in advance!!
Noloveformed has it absolutely right, but you could put this formula in tab 3 if you want with xlookup. Also, if you don't know vlookup very well, xlookup is the better one to use. It has a built in optional iferror function.
Just make sure you lock your references!
posted by bbqturtle at 6:25 AM on May 3, 2021 [5 favorites]
Just make sure you lock your references!
posted by bbqturtle at 6:25 AM on May 3, 2021 [5 favorites]
Best answer: This kind of survey tracking and reminder emails is an essential part of my job. I would absolutely use a vlookup for this.
Create a new column in Tab1; put your vlookup formula there linked to looking for the same email (nice unique ID) in Tab2. If it doesn't find it, have the formula display something like "Reminder Email needed". (In my surveys, I usually will display what date the reminder email should go out on, so that I can filter by that and sending rolling reminders for follow-up questionnaires.)
For the actual reminder email, I would filter to show only employees who need a reminder, copy values only to a new workbook and do the mail merge from the clean/no formula workbook (as a precaution).
I write my vlookup as follows, and it doesn't seem to matter whether the specific worksheets are reordered, provided that the formula column is never sorted separately from the reference data column.
Assuming your email columns are in A, with one row for headers, the formula for the first row would look like this:
=VLOOKUP(Tab1!A2,Tab2!A:A,1,0)
And if you drag down, it will update to A3, A4, A5, etc. And if the email appears in the other sheet, it will note it.
That said, this will leave you with a messy list of errors and the emails of people who have already completed it.
So a better formula would be
=IFERROR(VLOOKUP(Tab1!A2,Tab2!A:A,1,0),"Reminder Needed")
And now your people who have done the survey have their email address repeated, and the people who haven't have "Reminder Needed" in that column. Sort/filter, select just them, copy-past values only to a new sheet (I always use a new workbook just in case) and run your mail merge or big group email as needed.
Meanwhile, I will also add another column to my Tab1, call it "Reminder1" and add the date it was emailed to all those "Reminder Needed" people - because good chance that you'll be sending Reminder2 and Reminder3 - and it's good to be able to document the exact date you sent those.
posted by jb at 7:13 AM on May 3, 2021 [7 favorites]
Create a new column in Tab1; put your vlookup formula there linked to looking for the same email (nice unique ID) in Tab2. If it doesn't find it, have the formula display something like "Reminder Email needed". (In my surveys, I usually will display what date the reminder email should go out on, so that I can filter by that and sending rolling reminders for follow-up questionnaires.)
For the actual reminder email, I would filter to show only employees who need a reminder, copy values only to a new workbook and do the mail merge from the clean/no formula workbook (as a precaution).
I write my vlookup as follows, and it doesn't seem to matter whether the specific worksheets are reordered, provided that the formula column is never sorted separately from the reference data column.
Assuming your email columns are in A, with one row for headers, the formula for the first row would look like this:
=VLOOKUP(Tab1!A2,Tab2!A:A,1,0)
And if you drag down, it will update to A3, A4, A5, etc. And if the email appears in the other sheet, it will note it.
That said, this will leave you with a messy list of errors and the emails of people who have already completed it.
So a better formula would be
=IFERROR(VLOOKUP(Tab1!A2,Tab2!A:A,1,0),"Reminder Needed")
And now your people who have done the survey have their email address repeated, and the people who haven't have "Reminder Needed" in that column. Sort/filter, select just them, copy-past values only to a new sheet (I always use a new workbook just in case) and run your mail merge or big group email as needed.
Meanwhile, I will also add another column to my Tab1, call it "Reminder1" and add the date it was emailed to all those "Reminder Needed" people - because good chance that you'll be sending Reminder2 and Reminder3 - and it's good to be able to document the exact date you sent those.
posted by jb at 7:13 AM on May 3, 2021 [7 favorites]
If you have Office 365, Excel has a FILTER() function you could put on Tab 3 to generate the list you want. Something like
posted by flabdablet at 7:33 AM on May 3, 2021
=FILTER(TAB_1!EMAIL_SENT, ISNA(MATCH(TAB_1!EMAIL_SENT, TAB_2!TASK_COMPLETE, 0)), "All completed")in a single cell of Tab_3 should work; FILTER() spills its results into subsequent cells. I don't have Office 365 so I can't test this, unfortunately.
posted by flabdablet at 7:33 AM on May 3, 2021
JB - if you haven't checked it out, xlookup is the new vlookup and has a built in iferror and columns numbers are easier :) it will blow your mind! :)
posted by bbqturtle at 7:41 AM on May 3, 2021 [2 favorites]
posted by bbqturtle at 7:41 AM on May 3, 2021 [2 favorites]
Response by poster: Thanks everyone! I was able to get this to work and it saved me a ton of time. I so appreciate your help.
posted by I_Love_Bananas at 3:11 PM on May 3, 2021
posted by I_Love_Bananas at 3:11 PM on May 3, 2021
This thread is closed to new comments.
Once you have a column with the data, apply a filter and presto, just the non-responses.
posted by noloveforned at 5:54 AM on May 3, 2021 [10 favorites]