Google-sheets – What’s a better way to check for the presence of a value across all rows

google sheets

I have a table that, for 30 people, marks their dietary needs. The first 3 rows are diet category, the remainder are restrictions:

----------|Jane|Joe|Ali|
Vegetarian| 1  |   |   |
Vegan     |    | 1 |   |
Omnivore  |    |   | 1 |
No gluten |    |   | 1 |
No nuts   |    | 1 |   |

Now I am trying to create lists of which restrictions are associated with which dietary type- the result will be, e.g:

Vegan: No nuts
Vegetarian: 
Omnivore: No gluten

I've done this in a clumsy way by specifying a separate rule for every restriction- this prints the Column A value (name) of a restriction if there is any person who has that restriction and is also vegetarian: 

| Vegetarian | =if(SUM(FILTER(C2:AF2,NOT(ISBLANK(C5:AF5)))), A5 & " ", "")&if(SUM(FILTER(C2:AF2,NOT(ISBLANK(C6:AF6)))), A6 & " ", "")&if(SUM(FILTER(C2:AF2,NOT(ISBLANK(C7:AF7)))), A7 & " ", "")
| Vegan      | =if(SUM(FILTER(C3:AF3,NOT(ISBLANK(C5:AF5)))), A5 & " ", "")&if(SUM(FILTER(C3:AF3,NOT(ISBLANK(C6:AF6)))), A6 & " ", "")&if(SUM(FILTER(C3:AF3,NOT(ISBLANK(C7:AF7)))), A7 & " ", "")

Considering that the only difference for all the rules in the vegetarian food restrictions count is the column number, I'm sure there's a more effective way to do this, but I haven't worked it out.

In pseudocode, what I'm looking for is something like this:

A3..A5.for_each do |row_num|
 if(SUM(FILTER(C2:AF2,NOT(ISBLANK(Crow_num:AFrow_num)))), Arow_num & " ", "")
end

Best Answer

I think the following does the job: 

=join(", ", filter(A$6:A$10, mmult(N(C$6:AF$10), N(transpose(C2:AF2)))))

The key part is matrix multiplication. Multiplying the array of preferences by the transpose of the row such as C2:AF2 results is nonzero elements corresponding to the rows where there is an overlap with C2:AF2. Then the column with the names of restrictions is filtered by the result of multiplication, and the results joined, separated by comma-space. The N() function performs conversion of blank cells to zeros, otherwise mmult complains about non-numeric numbers.

The formula uses absolute row references for 6-10 which in my example are the rows with restrictions. The reference to row 2 (vegetarian) is relative. So, copy-pasting this formula down the column will result in correct answers for other diets.

Related Topic