Pull the last value from multiple waterfall columns into a new column

[HankaRPA]

Hi all,

I am attempting to build a workflow that takes this data in a "triangle" or waterfall type format into a new column. The aspect I'm struggling with here is that I only need to pull the last value (that's not null or 0) for each column. Lastly, I'm trying to pull the value for the right-most column first, and proceed to the next column on the left for the next value.

I tried using a multi-row formula along with an IF statement: IF [F17] = 0 THEN [Row-1:F17] ELSE [F17] ENDIF ; but I'm getting duplicate values for the multi-row. I considered using nested IF statements as well, but I think that would continue to pull in a singular column's last value (output below; new column trying to fill out is "Cumulative Incurred Losses"). I uploaded a dummy excel file with the input file format. Any guidance would be greatly appreciated. Thank you in advance

[princejindal · Accepted solution]

Hope this helps!

Thanks!

Edit : Updated the correct workflow.

[HankaRPA]

Sample field is a tool I haven't seen utilized/needed to use yet; looks like that was the trick to pulling the last values. Thanks @princejindal !

[princejindal]

Glad it helped you. I believe you can also use summarize tool "Groupby" Name and select "Last" for Value.