Using Google Forms we have a list of info on what events people are interested in by allowing respondents to check off the boxes to the events they like or add their own events. This column of data from Google Forms has some cells with one event, other cells with lists of event names, and some with a combination of the two and with new event ideas, not on the Google Form. The point of the dataset I'm trying to make is to show all the event ideas that are different from the provided options in the google form; so one of the options is "Coffee Social" but I don't want it and others to show up in the final list, only event names that respondents create that are different.

The current formula I've made is: =TRANSPOSE(UNIQUE(SPLIT(JOIN(",",'Form Responses 1'!X2:X),","),true)). This is able to provide a column of values that are close to what I want but I still run into 2 problems.

Problem 1: Some values have spaces either at the end or beginning of the event name which allow for both " Move Night" and "Movie Night" to be in the list when it clearly is wrong.

Problem 2: I don't know how to keep the already established event names out of the final list. IDK which functions to use and where in the formula to use them to keep them from being in the final list.

PlS help!☺


1 Answer 1


Use filter(), like this:

  events, remove, 
  filter(events, match(events, remove, 0)) 
    unique( trim( transpose( split( join(",", 'Form Responses 1'!X2:X), ",", true ) ) ) 
  { "event name to remove 1"; "event name to remove 1"; "event name to remove 3" } 

Your Answer

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

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