Fastest way to merge multiple columns with exclusive NULL/non-NULL combos

[Rajko]

Hi, I have some columns that looks as follows, due to changes in an underlying data model:

Col 1   |    Col 2

  XX    |   

           |    YY

  ZZ     |    

           |    QQ

I want to make it like this:

Col 3 

XX   

YY

ZZ

QQ

Basically, if one col has a value, the other is always Null. The things is, I possibly have 25 pairs of columns like this and would really like to avoid manually making a IF (NULL) Then ... formula for every single one. Any tips on a fastest way to do this? Ideally all I would manually do is specify the row combinations...

One thing that has occurred to me is transposing the columns to rows, then change the names of pairs to be the same, then cross-tabing back.. Alternatively, concatenating the columns but I need to be careful about NULL vs. empty strings and types 😕

I guess I might end up doing that, but curious if I am missing anything else + trying to learn best practices! 

Thank you!

Rajko

[kelsey_kincaid]

Hi @Rajko ,

Does the below represent your desired output?

Col 1   |    Col 2

  XX    |   YY

  ZZ     |    QQ

[Rajko]

sorry, realized I didnt clarify that, adding now!

[AngelosPachis · Accepted solution]

Hi @Rajko ,

Assuming that your input looks something similar to the following:

then I believe the attached workflow will return you the output you are looking for

Hope that helps, let me know if that worked for you.

Regards,

Angelos

[Rajko]

Yeah thats awesome thanks so much!!

[kelsey_kincaid]

Hi @Rajko ,

Something like this should work and would work for any number of columns. Let me know what you think!

Edit: Whoops! Looks like @AngelosPachis beat me to it. Glad you got what you needed!

[Rajko]

Im looking at your multirow formula and don't understand why the columnIDs start repeating after it its 6? What causes the loop to reset?

[Rajko]

Still interesting for me to see approaches, thank you!

[AngelosPachis]

Great questions @Rajko ,

If you look at the configuration window of the multi-row formula tool, I have grouped on the field Record ID.

That causes the calculation to reset, for each distinct Record ID value in my dataset, and that's what causes the Column ID to start repeating after 6.