Please help me with a formula to replace a 0 value cell with a non-zero value from preceding non-zero cell value or from the next cell. End result should be all cells having a non-zero value even if one cell has a non-zero value. If all cells are zero then ignore it and keep it zero
Appreciate your help thanks
Solved! Go to Solution.
Hi @Mahesh6279
This can be done without the help of Macros.
Below are the steps.
Step 1: Input
Step 2: Use the Transpose tool
Step 3: Use Tile tool to do the numbering
Step 4: Use Sort tool to sort by descending based on the above Tile tool results
Step 5: Use Multi-Row formula to overwrite 0 with previous cell value.
Step 6: Use Sort tool to sort by ascending
Step 7: Use Crosstab tool to get your desired output.
Many thanks
Shanker V
Hi @Mahesh6279 , you can do this with the multi-row formula tool - you just have to transpose your data first. Hope this helps!
@FinnCharlton this is not working if the first month is 0
@Mahesh6279 One way of doing this
@binuacs thanks a lot sir. this is working fine
@binuacs i encountered a problem. after applying the formula the numbers from the above component 1 is getting filled for component 2
@Mahesh6279 Make sure that the group by option is selected in the multi-row tool