Row by row comparison with FILTER returning error

Question

I am trying do a row by row comparison of two separate sheets, and display the unequal rows on a third sheet. As an example, rows three and five on Sheet1 are different from Sheet2 and I want to display only them on Sheet3.

      SHEET1                    SHEET2
   A    B    C               A    B    C
1  INT  STR  BOOL         1  INT  STR  BOOL
2  1    A    TRUE         2  1    A    TRUE
3  2    B    FALSE        3  2    B    TRUE
4  3    C    TRUE         4  3    C    TRUE
5  4    D    FALSE        5  C    3    FALSE

So I would want Sheet3 to look:

      SHEET3
   A    B    C
1  INT  STR  BOOL
2  2    B    FALSE
3  4    D    TRUE

Right now the formula that I am using is this:

=ARRAYFORMULA(FILTER(IMPORTRANGE("URL1","A2:C5"), IMPORTRANGE("URL1","A2:C5")<>IMPORTRANGE("URL2","A2:C5")))

where the proper links have been substituted for "URL1" and "URL2" for brevity in the above formula. This formula is giving a #VALUE error with the explanation:

FILTER range must be a single row or a single column.

Does anyone know a way to fix this?

Answer 1

The formula filter sorts in one direction only; its second argument must be a one-dimensional range of booleans. So you would need to compare columns individually; this quickly becomes ugly with all the importranges:

(IMPORTRANGE("URL1","A2:A5")<>IMPORTRANGE("URL2","A2:A5")) + (IMPORTRANGE("URL1","B2:B5")<>IMPORTRANGE("URL2","B2:B5")) + (IMPORTRANGE("URL1","C2:C5")<>IMPORTRANGE("URL2","C2:C5"))

So I suggest using query instead, which allows for more complex queries. The most important limitation of query is that the contents of each column must be of the same type (other types will be treated as Null values). I gather from INT, STR, BOOL in your data that this is the case. So, this is what the query would look like:

=query({IMPORTRANGE("URL1","A2:C5"), IMPORTRANGE("URL2","A2:C5")}, "select Col1, Col2, Col3 where Col1 <> Col4 or Col2 <> Col5 or Col3 <> Col6")

Here the first argument is obtained as the join of two arrays (they must have the same number of rows), so it has 6 columns. The second argument is the query string.

Answer 2

Short answer

=ArrayFormula(
  {Headers;
   FILTER(Sheet1,
     ISNUMBER(
       MATCH(
         Transpose(QUERY(Transpose(Data1),,1E+100)),
         Transpose(QUERY(Transpose(Data2),,1E+100)),
         0
       )
     )
   )
  }
)

Explanation

When a formula doesn't work it should be simplified to make it easier to find where the error is occurring.

In the case exposed by the OP, the IMPORTRANGE parts could be moved out of the formula, like moving to another part of the spreadsheet.

In this answer use the following names are used to make the formulas more readable.

• Headers is a 1 X 3 range.
• Data1 is the data from returned by IMPORTRANGE("URL1","A2:C5"). This have three columns and three rows.
• Data2 is the data returned by IMPORTRANGE("URL2","A2:C5"). This have three columns and three rows.

The simplified version of the formula exposed by the OP is

=FILTER(SHEET1,SHEET1<>SHEET2)

The error reported by the OP occurs because the second argument of FILTER should be an array of one column (n X 1, where n is the number of rows).

One way to resolve the problem is to concatenate the columns and for that in this answer the proposal is to use the following construct:

 Transpose(QUERY(Transpose(Data1),,1E+100))

The above construct takes advantage of feature of QUERY that concatenates the rows declared as headers. The first inner Transpose makes the rows becomes columns so they are treated as headers. The second argument is empty and the third argument is a number bigger enough to empconpases all the rows, in this case all the columns of Data1. The outer Transpose makes the result of QUERY becomes an array of one column.

Using a comparator like <> in an array formula compares row by row, but assuming that SHEET1 and SHEET2 could have different number of rows could be better to use MATCH instead.

ISNUMBER will return TRUE when a math was found and FALSE otherwise. As it will return a single result for each cell when an array is used as argument, so in this case it will return an 3 X 1 array.

The proposed formula in the short answer section is easily adapted to source arrays of any number of columns and rows. The requirement is that SHEET1 and SHEET2 have the same number of columns.