Multi-Row formula for Multiple columns at the same time

[girisri]

Hi,

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

[clant]

You could transpose your 10 columns, update them with one then cross tab it back out but this will make your multi row formula more complicated

cheers

chris

[girisri]

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)

Problem						Expected Result
Name	Col1	Col2	Col3			Name	Col1	Col2	Col3
N1	Monthly	Quarterly	Annual			N1	Monthly	Quarterly	Annual
N1						N1	Monthly	Quarterly	Annual
N1						N1	Monthly	Quarterly	Annual
N2	Quarterly	Annual	Monthly			N2	Quarterly	Annual	Monthly
N2						N2	Quarterly	Annual	Monthly
N3	Annual	Monthly	Quarterly			N3	Annual	Monthly	Quarterly
N3						N3	Annual	Monthly	Quarterly
N3						N3	Annual	Monthly	Quarterly
N4	Monthly	Quarterly	Annual			N4	Monthly	Quarterly	Annual
N4						N4	Monthly	Quarterly	Annual
N4						N4	Monthly	Quarterly	Annual

[danilang · Accepted solution]

@clant has the right idea

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.    

1. Before you transpose, generate a unique row number[RowNumber]
2. Transpose to give you name and value column using [RowNumber] as the key 
3. 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.  
4. 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
5. Apply your multiRow formula to calculate the new values, make sure to group by Name. 
6. Sort your values by [RowNumber] and [ColumnNumber] to get the original order
7. 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

Dan

[girisri]

Thank you, great solution.

However I have  50+ columns, so need to weigh in 50+ Multi-row formula for each column or the transpose approach

[Kenda]

Hey @girisri

Check out this macro created by @patrick_digan. I think it can accomplish what you're looking for with one tool. 

[girisri]

Thanks! 

[dondapati]

Based on the you'r expected result from the Input ..The following will helpful.

Thank you

[Sunitha]

Hi Dan,

I have similar issue issue please help me to fix. i would like to consider all columns mentioned below. Attached workflow(v 2018.3) i'm considering period column only.

Thanks,

Sunitha