yogi_Create Row By Row 'Group Membership" From Set Of 'E Mail Entries'

Google Spreadsheet   Post  #2510

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-08-2018

question by: Thomas LIOT
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/ki3DWMKgJrc;context-place=mydiscussions
Regextract in third column if Col 1 and 2 empty. If col 3 empty too give a message.
Hi guys, 

I can't solve this one alone. 

This data comes from a google contacts CSV, and the goal is to add contacts to groups named as the value between "@" and "." of the email address... (Franck@google.fr goes into google group, Mike@other.com goes into "other" group

Group Membership	E-mail 1 - Type	E-mail 1 - Value	E-mail 2 - Type	E-mail 2 - Value	E-mail 3 - Type	E-mail 3 - Value
EXEMPLE	WORK	work-email-example@EXEMPLE.fr	HOME	home-email-example@home.fr	OTHER	Other-email-example@other.fr

If email one doesn't exist, create group from mail-type-2

Group Membership	E-mail 1 - Type	E-mail 1 - Value	E-mail 2 - Type	E-mail 2 - Value	E-mail 3 - Type	E-mail 3 - Value
HOME			HOME	home-email-example@home.fr	OTHER	Other-email-example@other.fr

And if email two doesn't exist either, get email 3

Group Membership	E-mail 1 - Type	E-mail 1 - Value	E-mail 2 - Type	E-mail 2 - Value	E-mail 3 - Type	E-mail 3 - Value
OTHER					OTHER	Other-email-example@other.fr

From this example, I can create the two first groups "WORK" and "HOME" but I have got #N/A on the third... 

Here's the formula: 

'=ArrayFormula(IFERROR(REGEXEXTRACT(AL2:AL;"@(.*)\.");

IF(ISBLANK(AL2:AL);REGEXEXTRACT(AN2:AN;"@(.*)\.");
IF(ISBLANK(AN2:AN;REGEXEXTRACT(AP2:AP;"@(.*)\."))))))

I have tried with operators AND / OR / NOT ... with no success. Also, what I wold like to do is to use the "iferror" to get a text such as "this contact doesnt have email addresses yet" and put that into a contact note in another column.

Not sure already, but I could also consider getting each group separated by comma (ie: work,home,other) if a contact as more than one email address but that's not mandatory at all.

Thanks for your help guys, I have already learnt a lot reading subjects here.