Google-sheets – Copy entire rows from one sheet to another if certain cell in first sheet does NOT contain certain text value

formulasgoogle sheetsgoogle-sheets-query

I have two sheets.
The first one has incoming data that dynamically updates. The fields are Name, Last Name, Email.
I want to duplicate the rows in a second sheet but only if the email field in sheet 1 does NOT end with or contain "@gmail.com".

Any idea on how can I accomplish this?

Best Answer

With importrange + regexmatch

Don't forget to allow access to connect the two sheets.

=filter(
IMPORTRANGE(SHEET URL,"Sheet2!A1:A3"),
not(arrayformula(regexmatch(IMPORTRANGE(SHEET URL,"Sheet2!A1:A3"),"@gmail\.com")))
)

Related Solutions

Google Sheets – Combine Data from Two Columns and Remove Blanks

Since the order of data is not important, the following will do:

={filter(A1:B, len(A1:A)); filter(C1:D, len(C1:C))}

Explanation:

filter(A1:B, len(A1:A)) gets all A-B pairs with nonempty A entry
the second filter does the same for C-D
={ ... ; ... } is concatenation of two arrays, putting one below the other. (See using arrays in Google Sheets.)

You can also sort the results by some field, e.g., by the first one, song name:

=sort({filter(A1:B, len(A1:A)); filter(C1:D, len(C1:C))}, 1, true)

Google-sheets – Need to pull rows from one tab to another tab in same spreadsheet that meet certain criteria

Copy and paste is tough, but you could probably do it in Apps Script. However, displaying the rows in the new tabs is absolutely the purview of the Filter function.

=Filter('Master Spreadsheet'!A:N,N:N="Rejected")

Replace the data there with your tab names, columns, and criteria.

Best Answer

Related Solutions

Google Sheets – Combine Data from Two Columns and Remove Blanks

Google-sheets – Need to pull rows from one tab to another tab in same spreadsheet that meet certain criteria

Related Topic