Google-sheets – How to list which values from field A don’t appear in field B? (Survey recipient responses)

google sheets

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.)

Best Answer

The problem is that split does not work with an array (unfortunately). There are workarounds though. In your case, replacing index-split with regexextract does the job:

=FILTER(A19:A32, NOT(COUNTIF(regexextract(B:B, "^\S+"), A19:A32)))

The regular expressiion ^\S+ means: get all non-whitespace characters at the beginning of the string, which is what you tried to do.


Aside: you have two Miguels on your list, so ignoring last names might not be such a good idea. What if one Miguel responds and the other doesn't?