Macro query

[EtamSoko]

hi Everyone,

I have an excel file with hunders of data columns, lets name the first is Data 1. Each column has a duplicate column next to it as Data 1 (new) where I am expecting to receive data input by other peers. 

I want to apply the logic for all columns that if !IsNull(Data 1 (new)) THEN Data 1 (new) , else Data 1. How can I create a macro that I could apply to all columns? And just to select in advance to which columns i need to apply the above logic?

thanks a lot!

MateK

[atcodedog05]

Hi @EtamSoko ,

You can solve this using Multi-Field tool.

Input:

Output:

Formula:

IF isnull([_CurrentField_]) THEN [Data1] ELSE [_CurrentField_] ENDIF

Workflow:

Select columns here

Iterative lesson on Multi Field tool : https://community.alteryx.com/t5/Interactive-Lessons/Multi-Field-Formulas/ta-p/424422

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[EtamSoko]

@atcodedog05 

thanks for the quick response.

Based on your workflow, what I need is Data 1 original column data to be overwritten if the column next to it is not null. 

before:

Data 1 Data 1 New            

1          1 

2          6

3          3

after:

Data 1 Data 1 New

1          1 

6          6

3          3

Also, as I mentioned , the same logic needs to be applied for hundreds of columns.

is it still doable with Multi Field too?

[atcodedog05]

@EtamSoko 

Oh its the other way around. No its not possible. Can you provide some data i can take a look in other ways.

[atcodedog05]

Hi @EtamSoko ,

I was able to achieve your requirement. It always takes the last column which is not NULL

Input:

Output:

Workflow:

Select the columns here in yellow highlighted region. In the orange tool.

Hope this near to your requirement. Please check and let me know.

[EtamSoko]

thanks for it, but still not the solution i need. I will try it once again:

Data 1 Data 1 new

A          B

B          Null

C          Null

Expected Result:

Data 1 Data 1 new

B          B

B          Null

C          Null

The logic would need to be applied to 100 columns which is if !Isnull([column name new]) THEN [column name new] else [column name] endif 

this logic should be packed up in a macro and I will want to select pair of fields which will go into the macro and applying the above logic. instead of creating 100 formulas in formula tool with the logic that I have just explained

hope this helps!

thanks

M

[atcodedog05]

Hi @EtamSoko 

If you check in the above output Data1 is following if !Isnull([column name new]) THEN [column name new] else [column name] endif 

i.e is Data 2 is not null is written to Data 1, if Data 3 is not null is written to Data 1.

So basically if the latest column value is not null it will overwrite on the previous value.

If you tell me where exactly its falling out i can help you out.