Replace Value with Next if Zero

[Caldrumr1234]

I have an Excel question that I am afraid is out of my range of expertise.
I have a list of items with inventory values.
What I need is either a formula or script that will say if the value is zero, then grab the value from the next line for the same item number if it is not zero, and continue down until it gets one (and stop).
If lines for that item are zero, it should retain the zero.
Can someone point me in the correct direction?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


In the example above, the first and second lines should have the zero replaced with 4.

Thanks in advance!

 

[tyija1995]

Hey,

Try in a new cell:

=INDEX($B$2:$B$5,MATCH(1,($A$2:$A$5=A2)*($B$2:$B$5<>0),0))

Ctrl+Shift+Entered (CSE)

Then drag that formula down.

Assuming $A$2:$A$5 are your item numbers and $B$2:$B$5 are your values - then write this in to cell C2 and drag down to C5.

 

[Fluff]

Maybe

Book1
	A	B	C	D
1	Item	Value		123
2	123	0		4
3	123	0
4	123	4
5	124	2
Sheet2

Cell Formulas
Range	Formula
D2	=INDEX(B2:B5,AGGREGATE(15,6,(ROW(B2:B5)-ROW(B2)+1)/((A2:A5=D1)*(B2:B5>0)),1))

 

[Akuini]

Here's a macro to try:

Sub a1108429a()
With Range("A2:B" & Cells(Rows.count, "A").End(xlUp).Row)
    va = .Value
    For i = UBound(va, 1) - 1 To 1 Step -1
        If va(i, 1) = va(i + 1, 1) And va(i, 2) = 0 Then va(i, 2) = va(i + 1, 2)
    Next
    .Value = va
End With

 

[Caldrumr]

Here's a macro to try:

Sub a1108429a()
With Range("A2:B" & Cells(Rows.count, "A").End(xlUp).Row)
    va = .Value
    For i = UBound(va, 1) - 1 To 1 Step -1
        If va(i, 1) = va(i + 1, 1) And va(i, 2) = 0 Then va(i, 2) = va(i + 1, 2)
    Next
    .Value = va
End With

Thanks, this looks great.
I'm trying to get it to fit my exact data.
Let me show you what the actual form looks like.
I tried changing the range from B to K, but it's not picking it up.
And thank you for all of the answers. You guys are great!

 

[Akuini]

Let me show you what the actual form looks like.
I tried changing the range from B to K, but it's not picking it up.

Ok, try this one:

Sub a1108429b()
Dim va, vb
Dim i As Long, n As Long

n = Range("A" & Rows.count).End(xlUp).Row
va = Range("A11:A" & n)
vb = Range("K11:K" & n)

    For i = UBound(va, 1) - 1 To 1 Step -1
        If va(i, 1) = va(i + 1, 1) And vb(i, 1) = 0 Then vb(i, 1) = vb(i + 1, 1)
    Next
Range("A11:A" & n) = va
Range("K11:K" & n) = vb

End Sub

 

[RossTattersall]

You haven't said how the values are generated, but assuming it's a formula. So what's wrong with just (in cell B2 for example)
=IF( = 0, B3, [formula]) Copied down? Will trip over if the bottom most value is 0, but you haven't said what to do in that case.

 

[Caldrumr]

Akuini, that is perfect.
Thank you so much for helping me with something that would have taken me numerous hours, if I had even been able to figure it out myself.
I really, really appreciate it! :D

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.