2

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

Improve this question

1 Answer 1

Reset to default
0

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?

Improve this answer
3
  • Okay, I tried that, but the results are no different: it's still just showing the full list of respondents, rather than the list of those who haven't responded. Thanks for the reply - any other suggestions? By the way- I don't see in the documentation of SPLIT whether or not it works with arrays. Any thoughts on how I can tell which functions work on arrays and which don't as I explore the help menu?
    – joechoj
    Jan 30, 2016 at 9:00
  • You copied it incorrectly: it's \S, not /S. No, whether a command works with arrays is determined experimentally. The commands that make sense for arrays usually work, with split being a prominent exception.
    – user79865
    Jan 30, 2016 at 16:50
  • Ah, silly mistake! Thanks so much - this works great. (I'd upvote your answer, but don't have enough 'reputation' yet!)
    – joechoj
    Jan 30, 2016 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.