Data cleansing - multi-row formula help

[dataviolet]

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.

Dept	Description	Value	Code	What I want
	Balance Forward
	*** FISCAL YEAR END *** Opening Balance for Year	0		110105-1201
		0		110105-1201
** Account Totals		0	110105-1201	110105-1201
	110105-1201
	Balance Forward
	Opening Balance for Year	0		110105-1501
		0		110105-1501
** Account Totals	*** FISCAL YEAR END ***	0	110105-1501	110105-1501
	110105-1501
	Balance Forward
	Sample Data	0		110105-1506
		0		110105-1506
	Sample Data	0		110105-1506
		0		110105-1506
	Sample Data	0		110105-1506
	Sample Data	0		110105-1506
	Sample Data	0		110105-1506
		0		110105-1506
	Sample Data	0		110105-1506
		0		110105-1506
** Account Totals		0	110105-1506	110105-1506
	110105-1506

[fmvizcaino · Accepted solution]

Hi @dataviolet ,

Here is a suggestion for you.

Best,

Fernando Vizcaino

[echuong1 · Accepted solution]

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 solution]

Hi @dataviolet 

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

Pedro.

[dataviolet]

thank you all for the help! looks like the trick is to reverse the order. I really appreciate the explanations and solutions from everyone!