How to fill blank rows

Question

Hi,

I've my input data like below

ACCOUNT TYPEACCT ALIASNAssetA1019940 - Cash And Float0 A1020320 - Due  A1695000 - Total Loans2376050404 A2121900 - Goodwill1298504 A2295000 -not subject to currency revaluation0 A2893000 -  subject to currency revaluation7446591.27 A1100980 - Total Int0Asset Total 2384795499 Ties to support scheduleA3 & A4   Sum of YTD BAL FLAGACCOUNT TYPEACCT ALIASNLiabilityA4590000 - Total long-term debt0 A4495000 - Accrued Expense0Liability Total 0 Ties to support scheduleA3 & A4   Owner's EquityA4948800 - Equity0Owner's Equity Total 0 Ties to support scheduleA3 & A4

Under one Account type, there are some ACCT ALIAS and N. And account type appears only once in their respective first record

Is it possible to change this to below output?

ACCOUNT TYPEACCT ALIASNAssetA1019940 - Cash And Float0AssetA1020320 - Due AssetA1695000 - Total Loans2376050404AssetA2121900 - Goodwill1298504AssetA2295000 -not subject to currency revaluation0AssetA2893000 -  subject to currency revaluation7446591.27AssetA1100980 - Total Int0Asset Total 2384795499 Ties to support scheduleA3 & A4   Sum of YTD BAL FLAGACCOUNT TYPEACCT ALIASNLiabilityA4590000 - Total long-term debt0LiabilityA4495000 - Accrued Expense0Liability Total 0 Ties to support scheduleA3 & A4   Owner's EquityA4948800 - Equity0Owner's Equity Total 0 Ties to support scheduleA3 & A4

SPetrie · Answer

A multi-row formula would be able to handle it.

I copied and pasted your sample data so it may not be formatted exactly as you have it, but the formula will be essentially the same.

I did a data cleansing tool for that first column ,so my formula is looking for empty cells, yours may be null so alter it as you see fit.

The formula I used is

if isempty([ACCOUNT TYPE]) and !isempty([Row-1:ACCOUNT TYPE]) and !contains([Row-1:ACCOUNT TYPE],"Total") then [Row-1:ACCOUNT TYPE] else [ACCOUNT TYPE] endif

In plain terms, if the cell is empty and the one above it isnt and the one above doesnt have the word total it it, copy the cell from above. If none of those conditions are true, use whatever is currently in the cell already, be it blank or not.

Luke_C · Answer

Hi @Pandey19

You can use a multi-row tool:

New Workflow11.yxmd

AkimasaKajitani · Answer

Hi @Pandey19

In this case, you can use Multi-Row Formula tool.

But it is needed the little complex formula.

This formula will work well.

IF IsEmpty([ACCOUNT TYPE]) AND Contains([Row-1:ACCOUNT TYPE],"Total") THEN [ACCOUNT TYPE]
ELSEIF IsEmpty([ACCOUNT TYPE]) THEN [Row-1:ACCOUNT TYPE]
ELSE [ACCOUNT TYPE] ENDIF