I need a function to do the following task.
values in Col A1 to A6 are 2500,0,65,550,0,900
I need a function to transform these vales in Co B1 to B6 like this:

B1=2500
B2=65
B3=550
B4=900
B5=blank
B6=blank
please don't suggest to use filter,sort options or using a macro.
I am not expert in row number identification basing on condition.I think row
and match function will give the appropriate results ,what I need.

=IF(ISERROR(INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100 <0,ROW($A$1:$A$100)),ROW(1:1)))),"";INDEX($A$1:$A $100,SMALL(IF($A$1:$A$100<0,ROW($A$1:$A$100)),ROW (1:1))))

Insert and hit CTRL+SHIFT+ENTER


"TUNGANA KURMA RAJU" skrev:

I need a function to do the following task.
values in Col A1 to A6 are 2500,0,65,550,0,900
I need a function to transform these vales in Co B1 to B6 like this:

B1=2500
B2=65
B3=550
B4=900
B5=blank
B6=blank
please don't suggest to use filter,sort options or using a macro.
I am not expert in row number identification basing on condition.I think row
and match function will give the appropriate results ,what I need.

Thank you excelnt.I am curious to know how this formula evaluates the disered
results,can you explain me step by step.You have used small function in this
formula,how it helps to get the results?Thank you so much.

"excelent" wrote:

=IF(ISERROR(INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100 <0,ROW($A$1:$A$100)),ROW(1:1)))),"";INDEX($A$1:$A $100,SMALL(IF($A$1:$A$100<0,ROW($A$1:$A$100)),ROW (1:1))))

Insert and hit CTRL+SHIFT+ENTER


"TUNGANA KURMA RAJU" skrev:

I need a function to do the following task.
values in Col A1 to A6 are 2500,0,65,550,0,900
I need a function to transform these vales in Co B1 to B6 like this:

B1=2500
B2=65
B3=550
B4=900
B5=blank
B6=blank
please don't suggest to use filter,sort options or using a macro.
I am not expert in row number identification basing on condition.I think row
and match function will give the appropriate results ,what I need.