CODEWITHSUNDEEP
if-statementgoogle-sheetsfiltercountcountif
Colm Troy
Colm Troy

Reputation: 1947

Diff 2 sets of rows in Google Sheets

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.

Upvotes: 1

Views: 115

Answers (2)

player0
player0

Reputation: 1

try:

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

enter image description here

and:

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

enter image description here

or interchange A:A & B:B

Upvotes: 2

David Morales
David Morales

Reputation: 670

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.

Upvotes: 0

Related Questions