# How can I assign a single cell to the split out contents of it's neighbor?

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: Ultimately I want the result in this form: If I had a temporary table that looks like this: 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: 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?

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. 