CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Gage Carpenter
Gage Carpenter

Reputation: 19

Google sheets filtering large data set

=FILTER('New List'!A2:BA871, NOT('Old List'!A:BA))

The Goal:

I have 3 sheets -Sheet 1: The New list of data I need -Sheet 2: The Old list of data I DONT need -Sheet 3: The place where the extraction will take place (Where I am inserting the formula)

The Problem: Some of the items from Sheet 2 (the old list), are in sheet 1 (the new list). I need to extract and remove the overlapping data that can be found in both sheets and put that data in the third sheet

Upvotes: 0

Views: 83

Answers (1)

doubleunary
doubleunary

Reputation: 18784

To show rows that appear in new list and also in old list, use filter(match()) to identify duplicates by a unique ID column — assuming column A here:

=filter('New List'!A2:BA, match('New List'!A2:A, 'Old List'!A2:A, 0))

To show rows that appear in new list but not in old list, use isna(match()) to identify missing entries by a unique ID column:

=filter('New List'!A2:BA, isna(match('New List'!A2:A, 'Old List'!A2:A, 0)))

To show rows that appear in old list but not in new list:

=filter('Old List'!A2:BA, isna(match('Old List'!A2:A, 'New List'!A2:A, 0)))

To merge the data and show all rows, removing duplicate rows, use unique():

=unique( { 'New List'!A2:BA; 'Old List'!A2:BA } )

Upvotes: 1

Related Questions