How to find the fist not null cell?

[LeiCheng]

I have many columns in my table, and want to find out the first not null data cell in these columns, and pick it out into a new column. Someone knows how to do it? As below table, the starting number is wanted result.

Name	no.1	no.2	no.3	no.4	no.5	the starting number
a	1	2	3	4	5	1
b		2	3	4	5	2
c			3	4	5	3
d				4	5	4
e	1	2	3	4	5	1
f	1		3	4	5	1
g	1			4	5	1

[MarqueeCrew]

@LeiCheng ,

 if you transpose your data (group by name), you can filter !IsNull(value) and then sample with Group by name and get first 1 record s. 

then you can create a formula for a new field starting number with a formula of "starting number". You're ready to cross tab with a group by starting number. Header is starting number and value of value. 

join this data back to your original data and you should have it. 

cheers,

mark

[MarqueeCrew · Accepted solution]

@LeiCheng ,

Here's a workflow to show you how to configure the tools.

Cheers,

Mark

[LeiCheng]

@MarqueeCrew thanks for your clear solution! I used Filter and  write a very long formula to solve this, but actually, your way is much more flexible for further use.

[daniellefzimmerman]

Thank you so much for posting your workflow. I modified it a bit for my specific needs, but the solution was a lifesaver. 

[JPSeagull]

This worked great for exactly a solution I needed, but I do have rows that are all blank and I want to include that if there are no values at all, to enter a string value (for example if all cells are empty/NULL then enter "Overdue"). 

What would be the best way to approach that?