IF/ LOOKUP FUNCTION - Excel 2000

[Chandrashekhar]

I am using Excel 2000,
In excel sheet I entered a data inbetween from A1 to A6 and, My Query is I
would like to pick up the data only I have entered excluding the empty cell
in ROW B1 to B6

For Example

ROW INPUT ROW OUTPUT
A1 B1 9
A2 9 B2 15
A3 B3 6
A4 15 B4
A5 B5
A6 6 B6

Please guide me which formula will help me.

 

[Biff]

Hi!

Enter this array formula with the key combo of
CTRL,SHIFT,ENTER in B1 and copy down until you get #NUM!
errors:

=INDEX($A$1:$A$6,SMALL(IF($A$1:$A$6<>"",ROW($A$1:$A$6)),ROW
(1:1)))

Biff

 

[Ken Wright]

Or expanding slightly to get rid of the #NUM errors

=IF(ISERROR(INDEX($A$1:$A$6,SMALL(IF($A$1:$A$6<>"",ROW($A$1:$A$6)),ROW(1:1)))),"",INDEX($A$1:$A$6,SMALL(IF($A$1:$A$6<>"",ROW($A$1:$A$6)),ROW(1:1))))

Now just copy down as far as your original data range.