Google-sheets – How to assign a single cell to the split out contents of it’s neighbor

google sheets

I have data that was generated from a Google Form. The data has two parts: first a list of items the recipient checked, and second a score they gave (1-10). I would like to find the average score per item that was checked.

Here is some sample data:

enter image description here

Ultimately I want the result in this form:

enter image description here

If I had a temporary table that looks like this:

enter image description here

then I would be able to compute my final answer. I used this answer to write the query =QUERY(E2:F10, "SELECT E, AVG(F) GROUP BY E LABEL E 'Reason', AVG(F) 'Average'")

I'm able to create almost what I want, but not quite. This is the closest I've gotten:

enter image description here

Which I can get using: =ArrayFormula(QUERY(TRANSPOSE(ARRAYFORMULA(Trim(SPLIT(LOWER(CONCATENATE($A$2:$B$5&",")),","))))&{"",""},"select Col1",0))

You can find all of this sample data here.

Would you please help me, either by transforming the data to be in the form I ultimately want it in (with the averages), or by helping me to create the temporary table in between?

Best Answer

I added a sheet called SO Test - Aurielle where you can view my results

So here is my suggestions - it requires 2 formulas and you would need to copy one of the formulas down as needed but otherwise its pretty simple:

In column A you enter this formula:

=UNIQUE(ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(JOIN(",",Sheet1!A:A),",")))))

What I am doing here is first joining all the values with a common delimiter, in this case a , so it creates one long string, then splitting by that same delimiter to create a long list of all possible keywords. I use trim to clean it up and remove any unnecessary formatting, or space.

I then use UNIQUE to get a list of all possible keywords.

In Column B i entered: 

=AVERAGEIF(ARRAYFORMULA(REGEXMATCH(Sheet1!A:A,A2)),"true",Sheet1!B:B)

What this does is check each value in column A to see if it contains the keyword to the left of it, REGEXMATCH is great for this because it globally checks whether that word is at all contained in the original string, ignoring any other characters or punctuation.

By using ARRAYFORMULA it converts the values to true or false, so if you were to expand and just show that formula by itself, it would say true,false,true, true, because food is contained in the 1st string, but not the 2nd, and is in the 3rd and 4th.

Using AVERAGEIF, we use that array as the condition to check, but direct it to the column next to it, as the condition to average.

enter image description here

Related Topic