I have a weekly survey I send out to the same list of people using Google Forms, and they populate a 'responses' spreadsheet. I delete the responses weekly, and new rows are added as new responses are created. I've included a master list of respondents below the summary row (A19:A32), which is not affected by adding/deleting of responses. I'd like to create a list of people who haven't yet responded. Here's the Google Sheets link.
I feel I'm very close, but can't figure out the last step. I've cobbled together code from here and here. I use the INDEX and SPLIT functions (discussed in the second source) to return first names only, since respondents sometimes use last names. I've gotten this to work properly, in isolation (cell F2).
The meat of the formula is the FILTER, NOT, and COUNTIF functions, a solution presented by Anthony-DiSanti. I got this formula to work on my 'raw' respondents list (in cell D19):
=FILTER(A19:A32, NOT(COUNTIF(B:B, A19:A32)))
When I say it works: it lists only those people who have not responded, or whose names don't match my list because they've included their last names also.
Now to resolve the name issue. But when I try (in cell C19) to integrate the functions to modify the names to isolate just the first name, it seems to invert the results: instead of only listing people who haven't responded, it lists everyone.
=FILTER(A19:A32, NOT(COUNTIF(index(split(B:B, " "),1,1), A19:A32)))
Obviously I'm pretty new at this, so any suggestions beyond the scope of my questions are welcome. (For instance, I'm not sure how to get around having two separate Miguels
on the list.)