IF formula to fill in blanks

[cabryan17]

Hello,

My data set includes a list of employees (name, employee ID, work email address, supervisor name, supervisor's employee ID, supervisor email address). If an individual is both an employee and supervisor, their information will appear in the employee data-related columns and the supervisor data-related columns.

ISSUE: Some supervisor emails are not in the original data set. Where there are blanks, I want to find the supervisor's email address in their employee email address field and use it to fill in the blanks in their supervisor email address field.

I've tried using the following IF formula (see what I'm trying to do in parentheses), but it does not insert the employee email address into the blank supervisor email address fields.

IF IsNull([Supervisor Email]    (I'm trying to say if the supv email field is empty)

AND [Supervisor Employee ID] = [Employee ID]    (compare the supv ID field to the employee ID field, and if they're equal....)

THEN [Supervisor Email] = [Employee Email]     (then the supv email should be filled with the employee email)

ELSE [Supervisor Email]     (else if the supervisor email is not empty, keep the populated supervisor email)

ENDIF

Any thoughts on how to correct this given what I'm trying to do? Is there a better formula option than IF?

Thanks in advance!

[binuacs]

@cabryan17 This is how you write your formula in the [Supervisor Email] field

IF IsEmpty([Supervisor Email])  AND [Supervisor Employee ID] = [Employee ID] THEN [Employee Email]     
ELSE [Supervisor Email] 
ENDIF

[ArnaldoSandoval]

Hi @cabryan17 

Have you tried the function IsEmpty, you may change your condition to this:

IF (IsNull([Supervisor Email]) Or IsEmpty([Supervisor Email]))

AND [Supervisor Employee ID] = [Employee ID]

THEN [Supervisor Email] = [Employee Email]

ELSE [Supervisor Email]

ENDIF

I highlighted red my suggested changes.

hth

Arnaldo

[cabryan17]

Thank you, @binuacs and @ArnaldoSandoval. I tried your suggestion but I am still seeing the blanks in the supervisor email address field. I considered that maybe I had some mismatched data types, but all are V string.

If you have any other thoughts, I welcome them!

[binuacs]

@cabryan17 There might be some space in the supervise email address. Can you try the below formula

IF IsEmpty(Trim([Supervisor Email]))  AND [Supervisor Employee ID] = [Employee ID] THEN [Employee Email]     
ELSE [Supervisor Email] 
ENDIF

[binuacs]

@ArnaldoSandoval ISEmpty() function is able to check whether a field is Null or blank, 

[ArnaldoSandoval]

@binuacs 

Interesting, I ran a quick test with this data; and the answer is Yes, IsEmpty detects empty and null fields, while IsEmpty(Trim()) is still good.

hth

Arnaldo

[RobertOdera]

Hi, @cabryan17 

Kindly consider something like the below:

IF !IsEmpty(TRIM([Supervisor Email])) THEN TRIM([Supervisor Email]) // the field satisfies as-is,so keep it but trim in case you have leading or trailing spaces

ELSEIF IsEmpty(TRIM([Supervisor Email])) AND IsEmpty(TRIM([Employee ID])) THEN "Fix Me" // tag for further evaluation as both fields are either null or blank

ELSEIF !IsEmpty(TRIM([Supervisor Email])) AND IsEmpty(TRIM([Employee ID])) THEN "Fix Me" // tag for further evaluation as your replacement/fill field is either null or blank, i.e., your output will still have nulls or blanks!

ELSEIF IsEmpty(TRIM([Supervisor Email])) AND TRIM([Supervisor Employee ID]) = TRIM([Employee ID]) THEN TRIM([Employee Email])     //compare the "cleaned" supervisor ID field to the "cleaned" employee ID field to evaluate if they're equal and then fill with the "cleaned" employee email

ELSE "Fix Me" //global catch-all for any other variant of error that might happen (this is rare)

ENDIF

Start the logic from what you know to be true before other treatments, IMHO.

I hope you find this helpful - Cheers!

[cabryan17]

Hi @RobertOdera, thank you for taking the time to review my case. I like your insertion of them TRIM function. It did not solve my issue of the employee email being used as a substitute data source for supervisor email when supervisor email is missing, but I appreciate your efforts.

[nagakavyasri]

@cabryan17  try cleansing the data like this which replaces Nulls with blanks