Remember Me?

#1
November 5th 04, 07:42 AM
 Chandrashekhar Posts: n/a
IF/ LOOKUP FUNCTION - Excel 2000

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.

#2
November 5th 04, 08:20 AM
 R.VENKATARAMAN Posts: n/a

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

#3
November 5th 04, 08:22 AM
 Biff Posts: n/a

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.

.

#4
November 5th 04, 12:02 PM
 Ken Wright Posts: n/a

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post cnuk Excel Discussion (Misc queries) 2 January 17th 05 09:07 PM Richard Excel Discussion (Misc queries) 1 January 5th 05 09:36 AM Doug Excel Discussion (Misc queries) 13 December 25th 04 11:20 PM Kellyc Excel Discussion (Misc queries) 0 December 1st 04 04:45 PM Drew H Excel Worksheet Functions 3 October 31st 04 07:55 PM

All times are GMT +1. The time now is 09:00 PM.