0

The following sheet is an example of what I'm trying to do : https://docs.google.com/spreadsheets/d/1B9a8OUvwciCAYURLZq-Jd2KeSUZsYlalAFNsANzRzNw/edit?usp=sharing

In the first sheet ("Matrix"), I have a matrix which associate Ref with Tests. In the second sheet ("Results") , I have a recap of the different Ref, and for each Ref, I want to see the associated Tests. For now, I manage to do it using a formula which uses FILTER (the function is in the "associated tests" cell).

But, in order to be able to add new ref without to have to copy the formula, I wanted to use an ARRAYFORMULA. And, obviously, it doesn't work with my actual formula because of the FILTER.

Does anyone have an idea on a way to achieve this ? Maybe QUERY could help me but I don't really know its capabilities

0

I solved my issue using a custom formula :

function getTests(reqRefs, matrix) {
  let outputArray = [];
  reqRefs.forEach(function(reqRef) {
    if (reqRef != "") {
      let column = matrix[0].indexOf(reqRef.toString()); // Index of the column of the requirement
      let replies = matrix.map(item => item[column] == "X"? item[0] : null).filter(item => item != null ).join("/");
      outputArray.push(replies);
    }
  });
  return outputArray;
}

The first parameter for this formula is column range with the ref for which I want the tests. The second is the matrix.

| improve this answer | |

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.