Replacing zeros with boundary numbers

[hasanlianar]

Hello,

I have rows of numbers as shown in the following examples:

1 0 0 0 1 0 0 0 0
0 0 0 2 0 0 2 0 0
3 0 3 0 0 0 0 0 0

I want to come up with a dynamic formula to fill zeros with boundary numbers. The result should be following:

1 1 1 1 1 0 0 0 0
0 0 0 2 2 2 2 0 0
3 3 3 0 0 0 0 0 0

I would appreciate if anyone could help,

Anar

 

[Joe4]

Are you looking to do this in another location, or over top of the existing data?

If over top the existing data, note that cells can have hard-coded values, or formulas in them, but never both at the same time. So if you want to do it over top the existing data, it will probably require VBA.
Are you OK with that?

Also, will the boundaries ALWAYS be matching?
For example, could you have something like this:
1 0 0 2 0 0 0 0 0

If so, what happens in that case?

And, will there only ever be 2 non-zero values in each row?

 

[hasanlianar]

I want to do this in another location. I highly prefer non-VBA solution - with formulas
Boundaries will always match, i.e. boundaries will always be the same number (1-1, 2-2, 3-3, etc.)
There will always be 2 non-zero values.

 

[shg]

[table="width:, class:grid"][tr][td="bgcolor:#c0c0c0"][/td][td="bgcolor:#c0c0c0"]

b​

[/td][td="bgcolor:#c0c0c0"]

c​

[/td][td="bgcolor:#c0c0c0"]

d​

[/td][td="bgcolor:#c0c0c0"]

e​

[/td][td="bgcolor:#c0c0c0"]

f​

[/td][td="bgcolor:#c0c0c0"]

g​

[/td][td="bgcolor:#c0c0c0"]

h​

[/td][td="bgcolor:#c0c0c0"]

i​

[/td][td="bgcolor:#c0c0c0"]

j​

[/td][td="bgcolor:#c0c0c0"]

k​

[/td][td="bgcolor:#c0c0c0"]

l​

[/td][td="bgcolor:#c0c0c0"]

m​

[/td][td="bgcolor:#c0c0c0"]

n​

[/td][td="bgcolor:#c0c0c0"]

o​

[/td][td="bgcolor:#c0c0c0"]

p​

[/td][td="bgcolor:#c0c0c0"]

q​

[/td][td="bgcolor:#c0c0c0"]

r​

[/td][td="bgcolor:#c0c0c0"]

s​

[/td][td="bgcolor:#c0c0c0"]

t​

[/td][td="bgcolor:#c0c0c0"]

u​

[/td][/tr][tr][td="bgcolor:#c0c0c0"]

2​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td="bgcolor:#ccffcc"]

1​

[/td][td="bgcolor:#ccffcc"]

1​

[/td][td="bgcolor:#ccffcc"]

1​

[/td][td="bgcolor:#ccffcc"]

1​

[/td][td="bgcolor:#ccffcc"]

1​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td][/td][td="bgcolor:#ccffcc"]k2: =if(a2 > 0, a2, (countif($a2:a2, ">0") = 1) * max($a2:$i2))[/td][/tr]
[tr][td="bgcolor:#c0c0c0"]

3​

[/td][td]

0​

[/td][td]

0​

[/td][td]

2​

[/td][td]

0​

[/td][td]

0​

[/td][td]

2​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

2​

[/td][td="bgcolor:#ccffcc"]

2​

[/td][td="bgcolor:#ccffcc"]

2​

[/td][td="bgcolor:#ccffcc"]

2​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#c0c0c0"]

4​

[/td][td]

0​

[/td][td]

3​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td="bgcolor:#ccffcc"]

3​

[/td][td="bgcolor:#ccffcc"]

3​

[/td][td="bgcolor:#ccffcc"]

3​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td="bgcolor:#ccffcc"]

0​

[/td][td][/td][td][/td][/tr]
[/table]

 

[hasanlianar]

Thanks a lot, shg