CODEWITHSUNDEEP
google-sheetsmultiple-columnsspreadsheet
Tony-basaletti
Tony-basaletti

Reputation: 11

Google sheets: Filter sheet so it shows or selects only columns containing string

I have a google sheet with many columns, and I want to select (or filter) all of the columns whose labels contain the string "xyz" in them. Is there any simple way to do this?

The only way I've been able to do this so far is by Cmd + F to search the sheet for "xyz" and then manually select all of the columns with my mouse.

Context: The data is from a survey where participants evaluated 10 products. For each product, they answered the same set of questions. The way the data reads out to a sheet, each question gets a column. So that means there are 10 columns, for example, where I'm asking "how likely would you buy this product?" I want to select all 10 columns that ask how likely they would buy x product so that I can copy these columns, paste them into a new tab, and analyze them. These 10 columns all contain in their labels the string "how_likely", so I'm trying to select these columns by somehow searching for all columns that contain that string.

Upvotes: 0

Views: 2138

Answers (1)

Aerials
Aerials

Reputation: 4419

You can achieve this by doing a couple of preparation steps as follows:

  1. Create a sheet where you list the unique part of the question title (eg. "how_appropriate" , etc.). I named it "QuestionsList" and it looks like this: enter image description here
  2. Create a sheet where you transpose the data in "FormData". I called this sheet "TransposedFormData" and it looks like this:enter image description here

Use =TRANSPOSE(FormData!A1:H16) in cell A2 for it. I wrote the header names for easier visualization, but they aren't required.

Now that you have the data prepared, lets get the desired output. In the sheet "DesiredOutput" in cell A1, write the following formula:

=Transpose(QUERY(TransposedFormData!A:P,"select * where A contains '"&QuestionsList!A1&"'",0))

Notice the desired output: enter image description here corresponds to only one question (the one listed in QuestionsList!A1). So you will need a "DesiredOutput" sheet for each of the question titles, and on each change the row number in QuestionsList!A* where "*" represents the row number of the question title you want.

Upvotes: 0

Related Questions