Data cleansing - multi-row formula help

Question

Hi all! I have some messy data below that I am trying to tag with an account code. Is anyone able to help me with how I should update a multi-row formula in order to get the column in red below ("What I want")? I tried doing this: iif([Value]!=null(),[Row+1:Code],null()), but instead of replicating the Code everywhere there is a value (value not null), it is shifting the Code 1 row up.

DeptDescriptionValueCodeWhat I want      Balance Forward                   *** FISCAL YEAR END *** Opening Balance for Year0 110105-1201  0 110105-1201** Account Totals 0110105-1201110105-1201 110105-1201    Balance Forward              Opening Balance for Year0 110105-1501  0 110105-1501** Account Totals*** FISCAL YEAR END ***0110105-1501110105-1501 110105-1501         Balance Forward              Sample Data0 110105-1506  0 110105-1506 Sample Data0 110105-1506  0 110105-1506 Sample Data0 110105-1506 Sample Data0 110105-1506 Sample Data0 110105-1506  0 110105-1506 Sample Data0 110105-1506  0 110105-1506** Account Totals 0110105-1506110105-1506 110105-1506

fmvizcaino · Accepted Answer

Hi @dataviolet ,

Here is a suggestion for you.

Best,

Fernando Vizcaino

echuong1 · Accepted Answer

The multi-row formula will process rows from top to bottom, so it makes it difficult to reference values with a varying number of rows that you're trying to grab values from. An easier way to get around this is to reverse the order of your records (since the value you're trying to grab will now be at the top). You can add a record ID and sort by this to reverse the data back and forth.

For the multi-row formula, you can use a conditional statement that says if the value is filled in and the code is missing, take the code from the row above.

pedrodrfaria · Accepted Answer

Hi @dataviolet

See below for my attempt. We invert the order to be able to better utilize the multirow. It becomes much easier.

Pedro.