I have the following input dataset:- Field 1Field 2Field 3Field 4Tes1Name1ID1Yes No YesTes2Name2ID2Yes No YesTes3Name3ID3Yes No Yes I need output in this way:- Field 1Field 2Field 3Field 4Tes1Name1ID1YesTes1Name1ID1NoTes1Name1ID1YesTes2Name2ID2YesTes2Name2ID2NoTes2Name2ID2YesTes3Name3ID3YesTes3Name3ID3NoTes3Name3ID3Yes 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.

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.

Workflow

Accepted answers

FrederikE

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.

New Workflow_20230616.yxmd

All comments

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

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.

New Workflow_20230616.yxmd

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)

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335