Find last value in column greater than zero and copy row

[swapnilk]

Hi,

I need help with a formula.

I have a column A with numeric data in it. i want to check column A for last value greater then zero and then I want to copy the entire row.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]100[/TD]
[TD]Apple[/TD]
[TD]Apple[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]52[/TD]
[TD]Red[/TD]
[TD]Red[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]46[/TD]
[TD]Orange[/TD]
[TD]Orange[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[TD]Yellow[/TD]
[TD]Yellow[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD]Green[/TD]
[TD]Green[/TD]
[TD]Green[/TD]
[/TR]
</tbody>[/TABLE]


With the above sample data i want to check in column A the last non zero value which in this case will be cell A3 and then copy A3:D3 to some other sheet.

Thanks!

 

[sheetspread]

Excel 2010
	A	B	C	D
1	100	Apple	Apple	Apple
2	52	Red	Red	Red
3	46	Orange	Orange	Orange
4	0	Yellow	Yellow	Yellow
5	0	Green	Green	Green
Sheet5

Excel 2010
	A	B	C	D
1	46	Orange	Orange	Orange
Sheet6

Cell Formulas
Range	Formula
A1	=LOOKUP(2,1/(Sheet5!$A$1:$A$5<>0),OFFSET(Sheet5!$A$1:$A$5,,COLUMN(A1)-1))

 

[swapnilk]

Thank you very much sheetspread the formula worked.

I forgot to mention there are some rows hidden and i want the formula to apply only to visible cells. The formula should search for last non zero value in column A (with hidden rows) ignoring the rows which are hidden.

Is it possible to achieve ?

 

[sheetspread]

If we hide row 4 of:

Excel 2010
	A	B	C	D
1	100	q	w	e
2	0	z	x	c
3	32	t	gg	f
4	2	j	k	v
5	0	p	o	i
Sheet5

to get:

Excel 2010
	A	B	C	D
1	100	q	w	e
2	0	z	x	c
3	32	t	gg	f
5	0	p	o	i
Sheet5

then:

Excel 2010
	A	B	C	D
1	32	t	gg	f
Sheet6

Cell Formulas
Range	Formula
A1	{=LOOKUP(2,1/(IF(Sheet5!$A$1:$A$5<>0,IF(SUBTOTAL(103,OFFSET(Sheet5!A1,ROW(Sheet5!$A$1:$A$5)-ROW(Sheet5!A1),0)),ROW(Sheet5!$A$1:$A$5)))),OFFSET(Sheet5!$A$1:$A$5,,COLUMN(Sheet5!A1)-1))}
Press CTRL+SHIFT+ENTER to enter array formulas.