Diff 2 sets of rows in Google Sheets-CodePudding

Is there a simple way of diff'ing 2 sets of rows of data in Google Sheets? e.g. Sheet 1 - contains 10 rows

One
Two 
Three
Four 
Five
Six 
Seven
Eight
Nine
Ten

Sheet 2 contains 13 rows

One
Three
Five
Six 
Seven
Eight
Nine
Ten
Eleven
Twelve
Thirteen
Fourteen
Fifteen

Ideally i'd like to be able to run some formula to diff the two data sets to identify the additions and deletions in the second data set.

CodePudding user response：

try:

=FILTER(A:A, COUNTIF(B:B, A:A))

and:

=FILTER(A:A, NOT(COUNTIF(B:B, A:A)))

or interchange A:A & B:B

CodePudding user response：

One option is to match values in both sets and then check if all have been found or not.

=AND(ARRAYFORMULA(IF(Sheet5!A1:A="",,IF(ISERROR(VLOOKUP(Sheet5!A1:A,Sheet6!A:A,1,false)),FALSE,TRUE))))

This formula will check if every value in Sheet5 is present in Sheet6.

If all of them have been found, then the result will be TRUE.