This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Lets say I have 10 columns and I need to use Multi-Row formula to replicate the values in multiple rows for each of the columns , Is there a way to do it one shot. Or we have to use Multi Row formula 10 times in the workflow? for each column
Thanks! but as you outlined, formula becomes more complicated. Just to elaborate with an example - The Problem is on the left and expected result on the right. How to achieve this using Multi-Row formula only once and not thrice in the workflow? (since the below example has 3 columns that needs repetition)
If your formulas are similar enough, you can get away a multirow formula that is not to complex. The trick here is to keep track of the original row and column orders in the transposed data.
Before you transpose, generate a unique row number[RowNumber]
Transpose to give you name and value column using [RowNumber] as the key
Immediately after, use a multirow formula to fill in sequential values for each transposed row [ColumnNumber], grouped by [RowNumber]. This gives you the fields to sort by to return to your original row/column order.
Then sort by [Name] and [RowNumber]. This gives you all the values that were in one column original following each other in the name/value pair of columns
Apply your multiRow formula to calculate the new values, make sure to group by Name.
Sort your values by [RowNumber] and [ColumnNumber] to get the original order
Crosstab to get the original rows and columns back. You may need to rename your columns after this since the Crosstab tool, replaces spaces in column names with "_".
The attached workflow applies the formula, if the previous number is even, then change the sign on the current number to both input columns
Hope this helps
Edit after seeing your response: This approach will fit your data, since the 3 multi-rows apply exactly the same formula, i.e. fill down of the previous non-null value. There is still the trade off between the complexity of the transpose-sort-multirow-crosstab approach and simply using X multi-row formulas. For 3 column its just easier to do each one individually. At some point though(10 Columns maybe) the transpose approach is just easier to maintain