=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.