Check if a column contains a specific String and change the null fields to that string.

[FernandoSantello]

Hello!

So, I wanna check if my column X contains the string "CB" or "CL" or "CV", and if it does, I want to change all the null fields to the corresponding string "CB" or "CL" or "CV". For example, if the column X contains "CV" in a field, all the null values of that column will turn into "CV". And don´t worry, there will be never CB or CL or CV in the same column.

Thanks for any help,

Fernando S.

[Shalz]

You can use multi row formula and for each of your column apply this condition. If that respective column is null then =column X value

[Morgan_Thomas]

**Revised solution posted below, don't use the one attached to this comment

Hey there, 

Attached is what I came up with. It would be easier if there wasn't risk of other non nulls being in the column, but based on your description, i figured there could be other non nulls in the column that were not CL, CV or CB. So what i've done is created a copy of the column and only populated the duplicate column it if it was in ('CL','CV','CB') and then did a multi row formula to copy those values up and down (unimpeded by any other non null values). Then i did a formula at the end to populate the original column with the value in the duplicate column if the original column was empty. 

It may not be super pretty but hopefully it can help!

Thanks,

Morgan

[Morgan_Thomas]

Actually after testing some more  this may not work. Looking into it further now though.

[Morgan_Thomas · Accepted solution]

I like this solution better anyway! Lol. This time i've just filtered the column for where it is ('CL','CV','CB'), used the unique tool so there would only be 1 cell with the value, then used the append tool to append it to each row in the original data set. Then using a formula to populate the empty fields of the original column with the correct value. This one I am more confident in. 

Thanks!

[apathetichell]

Transpose it with your column with "CB","CL",or"CV" as a key column.

if [value] =null() then [CB column name] else [value] endif

crosstab it back.

If there are rows with no "CB","CL," or "CV" you can filter out the nulls after transposing and use:

switch([CB column],[value],"CB","CB","CV","CV","CL","CL") - this would keep null()s where your CB column doesn't have one of the required values...

[FernandoSantello]

Works like a charm! Thank you so much for your time and help Morgan!