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

 

[awoohaw]

Do you mean D6 is zero?
but what is your objective? are you trying to rank the products of each month?

 

[Zubair]

Do you mean D6 is zero?
but what is your objective? are you trying to rank the products of each month?

Let me rephrase it again

Need formula in Output area to bring data from A2:B6 with excluding row with blank in B2:B6.

Mr. Excel.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	A	3
11	B	4
12	C	1
13	E	2
Sheet4

 

[awoohaw]

Okay. do you want formulas that return blanks in row 14. If not, then office 365 function FILTER is what I would do, but you do not have that.
But, you do have Power Query which you can filter your data and then return it. Although I'm not sure of your ultimate objective so I don't know if that would work for you or not. Have you explored Power Query?

 

[Zubair]

Okay. do you want formulas that return blanks in row 14. If not, then office 365 function FILTER is what I would do, but you do not have that.
But, you do have Power Query which you can filter your data and then return it. Although I'm not sure of your ultimate objective so I don't know if that would work for you or not. Have you explored Power Query?

I am using very simple excel I don't know advance level of excel, so not explored PQ.

What currently I am doing applying link with cell to bring data but I have 1000 more lines and want any formula

 

[Peter_SSs]

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	A	3
11	B	4
12	C	1
13	E	2
14
Zubair

Cell Formulas
Range	Formula
A10:B14	A10	=IFERROR(INDEX(A$2:A$6,AGGREGATE(15,6,(ROW(A$2:A$6)-ROW(A$2)+1)/($B$2:$B$6<>""),ROWS(A$10:A10))),"")

 

[Zubair]

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	A	3
11	B	4
12	C	1
13	E	2
14
Zubair

Cell Formulas
Range	Formula
A10:B14	A10	=IFERROR(INDEX(A$2:A$6,AGGREGATE(15,6,(ROW(A$2:A$6)-ROW(A$2)+1)/($B$2:$B$6<>""),ROWS(A$10:A10))),"")

Clapping!!!

Many thanks Peter its exactly what I needed.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

 

[Zubair]

You're welcome. Thanks for the confirmation.

Apologies for asking addition in formula,

Can we add ranking based on value (column B) on descending order in the same formula please?

Like shown below

Mr. Excel.xlsm
	A	B
10	B	4
11	A	3
12	E	2
13	C	1
Bring data excluding zero value

 

[Peter_SSs]

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