I'm looking for the solution to merge two cells. By creating a new column/cell, compare both values, if null then consider the value of other cell
Input -
Col1 | Col2
---------------
null | 1
null | 2
3 | null
4 | null
Desired output -
new_col
------------
1
2
3
4
Hello @radhadevi_majeti,
If your problem only involves 2 columns, you can use a Formula Tool, creating a new column with the expression: IF IsNull([Col1]) THEN [Col2] ELSE [Col1] ENDIF
Hope this helps!
Hi @radhadevi_majeti - You can also use a Data Cleansing tool and then a simple formula: [Col1]+[Col2]
It worked. Thank you!
I'm using 'Text to Columns' to split data to rows. Is there a way to consider multiple columns at the same time using this option. Else, what are the other options available to split to rows for multiple columns at the same time.
Can you provide a sample file and expected output so that we can help you better.
Input -
sno | col1 | col2
----------------------------------
1 | abc, xyz | def, ghi
2 | mno, pqr | stu, vwx
Output -
sno | col1 | col2
------------------------
1 | abc | def
1 | xyz | ghi
2 | mno | stu
2 | pqr | vwx
You can do something like this.
Workflow:
1. Splitting each column separately.
2. Adding sub id for each split of row.
3. Joining back based on sno and sub id
Hope this helps : )
Hi @radhadevi_majeti ,
The solutions provided by @atcodedog05 @ArtApa @JoaoLeiteV work great!
Additionally, you can try the below as another way of ensuring that the workflow is dynamic regardless of the number of columns.
Thanks,
Mike
@atcodedog05's solution should work great - but here's a totally different approach using the Tile tool...
@apathetichell I choose not to use tile tool because it creates more not needed column 😅 Just my preference.