How to bring down row without zero value

[Zubair]

Hi all,

I need formula to bring next row information if value is zero.

Like from the below example, D13 is zero, in the month of Feb I want B14 and D14 in Cell B27 and C27.

I have a list of 1000 products so not able to link each cell. please help

Book1
	B	C	D
2	Product	Jan	Feb
3	A	200	120
4	B	100	89
5	C	89	200
6	D	95
7	E	150	150
8
9	Rank
10	A	1	3
11	B	3	4
12	C	5	1
13	D	4
14	E	2	2
15
16	Jan
17	A	1
18	B	3
19	C	5
20	D	4
21	E	2
22
23	Feb
24	A	3
25	B	4
26	C	1
27	E	2
Sheet1

Cell Formulas
Range	Formula
C10:C14	C10	=IF(C3=0,"",RANK(C3,$C$3:$C$7,0))
D10:D14	D10	=IF(D3=0,"",RANK(D3,$D$3:$D$7,0))
C17:C21	C17	=C10
C24:C26	C24	=D10
C27	C27	=D14

 

[Zubair]

Will the values in column B always be different to each other or could there be ties?

different

 

[Peter_SSs]

different

Thanks. Try this

23 03 11.xlsm
	A	B
1	Input	Feb
2	A	3
3	B	4
4	C	1
5	D
6	E	2
7
8
9	Output
10	B	4
11	A	3
12	E	2
13	C	1
14
Zubair (2)

Cell Formulas
Range	Formula
A10:B14	A10	=IFERROR(INDEX(A$2:A$6,MATCH(LARGE($B$2:$B$6,ROWS(A$10:A10)),$B$2:$B$6,0)),"")

 

[Zubair]

Thanks. Try this

23 03 11.xlsm
	A	B
1	Input	Feb
2	A	3
3	B	4
4	C	1
5	D
6	E	2
7
8
9	Output
10	B	4
11	A	3
12	E	2
13	C	1
14
Zubair (2)

Cell Formulas
Range	Formula
A10:B14	A10	=IFERROR(INDEX(A$2:A$6,MATCH(LARGE($B$2:$B$6,ROWS(A$10:A10)),$B$2:$B$6,0)),"")

Thats really fantastic. Thanks again

 

[Peter_SSs]

Cheers.