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

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
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 & " ", "")

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.

  • This answer a) works and b) is a great explanation of how & why it works, which made it straightforward to implement into my spreadsheet and also taught me a bunch. Very much appreciated, thank you. – Ali H Mar 16 '17 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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