Excel Formula to find next value in range if certain cells have zero value

[Nauas]

Hi Everyone,
I have a small request and I am pretty sure many Excel champions can resolve this in minutes. I am extracting numbers from a range. Lets say following range. It has zero or blank values in between & also negative and positive values. If I apply large numbers and link the ranking to other cells it is stopping at cells having zero values and If i take if formula with large and small the ranking range goes irrelevant due to formula being dragged into different cell. I have around 300 rows and around 150 having positive and negative values. I want to compile a table which gives me only

Column A	Column B
A	800​
B	900​
C	405​
D	400​
E
F
G	-100​
H
I	80​
J	-190​
K

 

[jasonb75]

Please include your expected output / results in your sample.

Also, please click on your user name in the top right corner, then 'Account Details', tick the box(es) to show which versions of excel you use, then save.

 

[Nauas]

Hi Jason,
Thanks a lot. I am using Office 365 version and I have updated that in my account setting.
My expected result should be following but formula driven. It should automatically ignore zero values for E, F &H. Could be please help? Otherwise work around is quite a task which will require manual inputs at later stage. I work with huge set of data and this will make my life easy.

Column A	Column B	Expected result	Preferred expected result
A	800​	800​	900​
B	900​	900​	800​
C	405​	405​	405​
D	400​	400​	400​
E	0​	-100​	80​
F	0​	80​	-100​
G	-100​	-190​	-190​
H	0​
I	80​
J	-190​

 

[jasonb75]

First attempt, results look good

Book4
	A	B	C
1	Column A	Column B	Results
2	A	800	900
3	B	900	800
4	C	405	405
5	D	400	400
6	E	0	80
7	F	0	-100
8	G	-100	-190
9	H	0
10	I	80
11	J	-190
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=IFERROR(LARGE($B$2:$B$11,ROWS(C$2:C2)+IF(ROWS(C$2:C2)>COUNTIF($B$2:$B$11,">0"),COUNTIF($B$2:$B$11,0))),"")

 

[Nauas]

Hi Jason,
Thanks amazing. it is working fine on the model working. Let me use this in real working file and I am quite sure it will work fine.
Your help is very much appreciated.

Regards,
Nauas

 

[Nauas]

Thanks Jason once again. It is working fine.

 

[jasonb75]

You're welcome

I'm going to have another look at it to see if I can shorten the formula a bit without sacrificing efficiency or functionality.

In the event of duplicate values, should the results also be duplicated, or should it only show unique values?

 

[Nauas]

Hi Jason.
Luckily, there are very remote chances of duplicate values because of decimals. I worked well and serves the purpose. I would love to learn this formula and master that from you.
Thanks for your help. It saved me couple of hours work on monthly basis.

 

[Nauas]

Hello Jason,
Thanks for helping me couple of years ago. That formula really helped me. By the way, you were asking about removal of duplicates above. What can I add into that formula to remove duplicate.

 

[jasonb75]

Not sure why I didn't suggest something like this originally, unless either (or both) of us were not using 365 at the time.

Book1
	A	B	C
1	Column A	Column B	Results
2	A	800	900
3	B	900	800
4	C	405	405
5	D	400	400
6	E	0	80
7	F	0	-100
8	G	-100	-190
9	H	0
10	I	80
11	J	-190
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=SORT(UNIQUE(FILTER(B2:B11,B2:B11<>0)),,-1)
C9:C11	C9	=IFERROR(LARGE($B$2:$B$11,ROWS(C$2:C9)+IF(ROWS(C$2:C9)>COUNTIF($B$2:$B$11,">0"),COUNTIF($B$2:$B$11,0))),"")
Dynamic array formulas.