Create query with multiple answers for each cell

Question

So I have got 3 sheets with 2 predefined ranges as you can see them in the Example:

# RangeA    # RangeB    # Wanted Result
========    ========    ===============
A | B       A           A
--------    --------    ---------------
1 | a       a           a
2 | a       b           1
3 | a                   2
4 | b                   3
5 | b                   b
6 | b                   4
7 | c                   5
8 | c                   6
9 | c
...

Now I would like to have a Formular to get the wanted result I have been searching quite long time today already, but I wasn't successful. I hope there is anybody who may help me. I hope the example is clear enough to understand what i want to do.

Thanks in advance for your time.

Fine, edited the starting post. I do know this is a Q&A site, But I saw a lot of questions where exactly this is handled. Maybe because if you try to answer your own question you get a dialog. Changed it now and now I know how to handle such a situation for the next time. — Spectator
– Spectator, Commented Jan 23, 2015 at 14:35

Spectator · Accepted Answer · 2015-01-23 14:33:09Z

I solved it in the end with google apps script. The function I used is pretty simple just two for loops:

/*
 * Merge merges two arrays to get one list of wanted values
 * @param needle {array} is a list of wanted values
 * @param haystack {array} is a list of values and their group
 * @return returns a list of merged values in the format group, value,
 *         value, group ...
 **/
function Merge(needle, haystack) {
  var result = [];
  // Set default values to parameters if parameter is not set.
  needle = needle || [[]];
  haystack = haystack || [[]];
  // Filter the array and remove empty items. # RangeB
  needle = needle.filter(function(item){
    return item[0];
  });
  // Filter the second array and remove empty or incomplete items # RangeA
  haystack = haystack.filter(function(item){
    return item[0] && item[1];
  });
  // Merge both arrays to get the # Wanted Result
  needle.forEach(function(item){
    result.push([item[0]]);
    haystack.forEach(function(needle){
      if(item[0] == needle[1]) {
        result.push([needle[0]]);
      }
    });
  });
  // Check if the result has a length
  if(result.length > 0) {
    return result;
  }
  // else return null to overcome the #error message
  return null;
}

Collectives™ on Stack Overflow

Create query with multiple answers for each cell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related