Logical except/difference ranges in google spreadsheets

2
votes

I want to get logical (A - B) or (A \ B) in google spreadsheets.

So, having:

A:A

{1,2,3,4}

and

B:B

{2,3,5,6}

enter image description here

So my formula

=my_amazing_formula(A:A, B:B)

should return

{1,4}

(Elements of A not being present in B)

The question

How can I achieve this?

3
google-apps-scriptgoogle-sheets
You want to compare objects or arrays? {1,2,3,4} is not a valid method. – Juan Bravo Roig
Are speaking in terms of ranges as describe in Range Class, or elements of arrays as in Javascript Arrays or Javascript Objects. Please explain question in more detail. – Cooper
Hey @JakubKopyś have you managed to make this work with my answer? Please let me know if you have any other question/issue. Cheers – carlesgg97
@Juan It's spreadsheet array notation. Both {1;2} and {1,2} are valid arrays which are equal to JavaScript [[1],[2]] and [[1,2]] respectively. – TheMaster
You are right @TheMaster, I thought he wanted to define an array. – Juan Bravo Roig

3 Answers

2
votes

Since in there is no such data structure as a HashSet in Google Apps Script yet, this is a somewhat tricky problem to solve efficiently. You can opt for the quadratic solution, which for each item in the first range would iterate over the whole second one trying to find a 'match', in order to discard it. This would look somewhat like @Cooper 's solution.

As an alternative, and considering that Google Apps Script's objects preserve property insertion order, you can use the following code which should theoretically yield better performance results (specially for larger workloads).

function DIFFERENCE(range1, range2) {
  var o = {};
  for (var i=0; i<range1.length; i++) {
    for (var j=0; j<range1[0].length; j++) {
      if (!o[range1[i][j]])
        o[range1[i][j]] = true;
    }
  }

  for (var i=0; i<range2.length; i++) {
    for (var j=0; j<range2[0].length; j++) {
      if (o[range2[i][j]])
        o[range2[i][j]] = false;
    }
  }

  return Object.keys(o).filter(function f(key) { return o[key]; }).map(function(res) { return parseFloat(res) });
}

This function assumes that you are dealing with numbers. If you want it to work with strings as well, you can replace the last line of code by the following: return Object.keys(o).filter(function f(key) { return o[key]; });

You can also see a couple of examples here:

enter image description here enter image description here

2
votes

You could do this with inbuilt formula:

=FILTER(A1:A4,ISNA(MATCH(A1:A4,B1:B4,0)))
=FILTER({1;2;3;4},ISNA(MATCH({1;2;3;4},{2;3;5;6},0)))
  • FILTER out A1:A4, where
  • A1:A4 ISNot Available in B1:B4
1
votes

If you question involves arrays then try this:

function elementsOfANotPresentInB(a,b) {
  var a=a||[1,2,3,4];
  var b=b||[2,3,5,6];
  var c=[];
  a.forEach(function(e){if(b.indexOf(e)==-1){c.push(e);}});
  Logger.log(c);
  return c;
}