Fill down null rows of multiple columns with respective column values

[aroy061987]

I have the following input dataset:-

Field 1	Field 2	Field 3	Field 4
Tes1	Name1	ID1	Yes
			No
			Yes
Tes2	Name2	ID2	Yes
			No
			Yes
Tes3	Name3	ID3	Yes
			No
			Yes

I need output in this way:-

Field 1	Field 2	Field 3	Field 4
Tes1	Name1	ID1	Yes
Tes1	Name1	ID1	No
Tes1	Name1	ID1	Yes
Tes2	Name2	ID2	Yes
Tes2	Name2	ID2	No
Tes2	Name2	ID2	Yes
Tes3	Name3	ID3	Yes
Tes3	Name3	ID3	No
Tes3	Name3	ID3	Yes

I can fill down each column one by one with multirow formula, but I have around 40 such columns which needs to be filled down. How can I achieve the same without adding 40 multi-row formula tool.

[ShankerV]

Hi @aroy061987 

One way of doing this.

Many thanks

Shanker V

[aroy061987]

Yes, I have 40 columns in my data set, for each column then I would end up adding one multi-row formula tool. I am actually looking for a solution with which I can use one tool or a macro to update all the 40 columns at a go.

[ShankerV]

Hi @aroy061987 

Step 1: Input the data

Step 2: To remove the blanks in the input file, if you do not have in your input, you can ignore the step 2. 

This might be due to copy paste issue also.

Step 3:

IF IsEmpty([Field 1])
THEN [Row-1:Field 1]
ELSE [Field 1]
ENDIF

Repeat for field 2 and 3 also.

Many thanks

Shanker V

[FrederikE · Accepted solution]

Hey @aroy061987,

A dynamic way to do this is by Transposing it before the multirow. 

2 Small Notes: 

Cross Tab changes the order of the columns and changes " " to "_" this you might need to correct manually again if it occurs. 

[ShankerV]

Hi @aroy061987 

sorry my bad, didnt notice the last line mentioned for 40 columns.

I see my fellow friend @FrederikE has proposed a solution, check whether that works for you.

Many thanks

Shanker V

[aroy061987]

Thanks, But I am looking for something less time consuming as I have 40 such columns to update. As per my requirement I even can not remove the blank rows.

I need some hacks to update all these 40 rows at a go without adding 40 multi-row formula tools.

[PhilipMannering]

You can try transposing all your columns and then use one MultiRow Tool as per @FrederikE 's solution above.

[aroy061987]

Yes this worked perfectly for me!

Thanks a lot for this brilliant and simple solution.

[PhilipMannering]

Here's a Python Tool solution (just for kicks),

from ayx import Alteryx
import pandas as pd
df = Alteryx.read('#1')


#################################
df = df.replace(regex='^\s*$', value=pd.NA).ffill()


#################################
Alteryx.write(df, 3)