Hi all, I have an existing data table that looks like this. If the Middle Column is a No, then I would like to fill in the Customer ID Column with the last Customer ID. Input Yes / No / BlankCustomer ID1YesCustomer_12No 3No 4YesCustomer_25 6No 7No 8No The desired end product looks like this. Input Yes / No / BlankCustomer ID1YesCustomer_12NoCustomer 13NoCustomer 14YesCustomer_25 6NoCustomer_27NoCustomer_28NoCustomer_2 The presence of the empty row in Row 5 means that I was unable to use the Multi-row tool with expression (IF ISEMPTY([Customer ID]) THEN [Row-1:Customer ID] ELSE [Customer ID] ENDIF) because looking back one row is not sufficient when there is an empty row. Any guidance on how I can go about solving this? All inputs are much appreciated.

@EL1988 : Here's what I'd do: * Put a unique ID to the table. If the "Input" column is unique, I'd just use that. Otherwise, I'd use the Record ID tool. * Use a Filter tool to remove the blank rows. * Use the Multi-Row Formula tool like you described. * Use the Union tool to bring the blank rows (from Step 2) back. * (Optional) Use the Sort tool on the RecordID column to return the table to its original order. I think that, in this way, you might be able to handle any number of blank rows. 😊

Multi-row tool but with a blank row in between

EL1988

Hi all,

I have an existing data table that looks like this. If the Middle Column is a No, then I would like to fill in the Customer ID Column with the last Customer ID.

Input	Yes / No / Blank	Customer ID
1	Yes	Customer_1
2	No
3	No
4	Yes	Customer_2
5
6	No
7	No
8	No

The desired end product looks like this.

Input	Yes / No / Blank	Customer ID
1	Yes	Customer_1
2	No	Customer 1
3	No	Customer 1
4	Yes	Customer_2
5
6	No	Customer_2
7	No	Customer_2
8	No	Customer_2

The presence of the empty row in Row 5 means that I was unable to use the Multi-row tool with expression (IF ISEMPTY([Customer ID]) THEN [Row-1:Customer ID] ELSE [Customer ID] ENDIF) because looking back one row is not sufficient when there is an empty row.

Any guidance on how I can go about solving this? All inputs are much appreciated.

Tips and Tricks

Preparation

Accepted answers

Peachyco

@EL1988 :

Here's what I'd do:

Put a unique ID to the table. If the "Input" column is unique, I'd just use that. Otherwise, I'd use the Record ID tool.
Use a Filter tool to remove the blank rows.
Use the Multi-Row Formula tool like you described.
Use the Union tool to bring the blank rows (from Step 2) back.
(Optional) Use the Sort tool on the RecordID column to return the table to its original order.

I think that, in this way, you might be able to handle any number of blank rows. 😊

All comments

DawnDuong

hi @EL1988

Almost there, you can do this in 2 steps and you have already done step 1

Step 1: (IF ISEMPTY([Customer ID]) THEN [Row-1:Customer ID] ELSE [Customer ID] ENDIF)

Step 2: Update the [CustomerID] column as follow:

if isempty([Yes / No / Blank]) then null() else [CustomerID] endif

Step 2 add a "corrective" step to force any row where the [Yes / No / Blank] is empty to have the [CustomerID] to be empty as well.

Cheers,
Dawn.

EL1988

Thanks Dawn, but I realised that there are instances when there are also multiple blank rows in between. Hence it still doesn't work when it refers back to 1 line before.

I guess that complicates Step 1?

Thank you.

Peachyco

@EL1988 :

Here's what I'd do:

Put a unique ID to the table. If the "Input" column is unique, I'd just use that. Otherwise, I'd use the Record ID tool.
Use a Filter tool to remove the blank rows.
Use the Multi-Row Formula tool like you described.
Use the Union tool to bring the blank rows (from Step 2) back.
(Optional) Use the Sort tool on the RecordID column to return the table to its original order.

I think that, in this way, you might be able to handle any number of blank rows. 😊

EL1988

Thanks Peachyco, got it to work thanks to your suggestion.

Much appreciated to you and Dawn! 😀

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703