2

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?

1

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")))
)
| improve this answer | |
0

...The fields are Name, Last Name, Email. I want to duplicate the rows...

Please try the following formula (where Col3 is the Email one):

=QUERY(IMPORTRANGE("spreadsheet_url","range_string"),"where NOT Col3 contains '@gmail.com'")

(Do not forget to adjust the formula ranges accordingly)

| improve this answer | |
  • The formula now works as expected. Forgot the NOT:/ – marikamitsos Apr 9 at 7:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.