1

I'm trying to get rid of bad data in my inventory list.

Sheet A is a sheet that is a list of all my products.

Sheet B is a sheet, generated from another piece of software, that lists all of my products that have been sold

I need to generate a third sheet, Sheet C, that lists all the unsold products. So, the number of rows between Sheet B and Sheet C should add up to the total number of rows on Sheet A.

How do I cross-reference/filter Sheet A with B to achieve C?

Lounge9
  • 1,213
  • 11
  • 22

1 Answers1

1

The following formula returns the list of entries from column A of Sheet1 that are not present in column A of Sheet2: 

=filter(Sheet1!A2:A, isna(match(Sheet1!A2:A, Sheet2!A2:A, 0)))

Explanation: match returns error code #N/A for those entries in Sheet1!A2:A that are not found in Sheet2!A2:A. The isna function converts those to True boolean values. Finally, filter returns those rows for which the second argument evaluates to True.

The filter can return more than one column: it can be, for example, 

=filter(Sheet1!A2:Z, isna(match(Sheet1!A2:A, Sheet2!A2:A, 0)))

But match requires a one-dimensional range, so you need a column (here assumed to be A) which can be used as an identifier of a row.