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 |
Solved! Go to 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.
Hi @dataviolet
See below for my attempt. We invert the order to be able to better utilize the multirow. It becomes much easier.
Pedro.
thank you all for the help! looks like the trick is to reverse the order. I really appreciate the explanations and solutions from everyone!