Making multiple column values NULL based on certain conditions

[aliensurfer]

========================================================
| ID  |   Name    |         Email              | Right_Name |     Right_Email         |
========================================================
| 15  | John Doe | johndoe@email.dom  | John Doe       | johndoe@email.dom  |
========================================================

I wanted to make Right_Name and Right_Email (these are just two columns but in my actual data there are more than 20 columns) NULL if the Name and Email are the same. How could I do that in one fell swoop instead of having a formula for every column?

Thanks,

A

[SPetrie]

Are you trying to compare Name to Right_Name and then making Right_Name null, or does it need to be done in pairs where Right name and Right email are only null if they both match their counterparts?

Is it safe to assume that there will be a right column we need to compare to its non-right counterpart?

I like to dynamically build formulas for these types of situations and use a dynamic replace to add them in. For this example, I filtered out column names that have "Right_" in them, assuming those are the ones we really care about.

Then build the formula to look at the column that doesnt have Right_ in it for the comparison and set to null as necessary. Any new columns with Right_ in the name will automatically get added.

[aliensurfer · Accepted solution]

Thanks, @SPetrie . I used a multi-field formula for this. Basically if Name and Right_Name are the same AND Email and Right_Email are the same, I needed to make all the Right_XXX columns as blank.