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.

experts may give beter solution meanwhile

1. introduce a row at the top and give a name e.g. INPUT
2. your data will be a1 to a7
3. hightlight a1 to a7
4.click data(menu)-filter-autofilter
4.you get a small inverted arrow at INPUT
5. click that arrow and click <nonblanks
7. you will get only non blank rows in columnA
7.highlight those visible cells and click edit copy
8. highlight B1 and click edit -paste
9.you may get only 15 dont worry
10.again data-filter-autofilter
11. that means autofilter is removed from column a
12. your B column will have what you want

is it ok try it .
==============
Chandrashekhar wrote in message
...
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.

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

-----Original Message-----
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.

.

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,SM ALL(IF($A$1:$A$6<"",ROW($A$1:$A$6)),ROW(1:1))))

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Biff" wrote in message
...
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

-----Original Message-----
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.

.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004