Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 5th 04, 07:42 AM
Chandrashekhar
 
Posts: n/a
Default 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   Report Post  
Old November 5th 04, 08:20 AM
R.VENKATARAMAN
 
Posts: n/a
Default

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.



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

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   Report Post  
Old November 5th 04, 12:02 PM
Ken Wright
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2002 and 2000 co-install. Control Which Starts ? cnuk Excel Discussion (Misc queries) 2 January 17th 05 09:07 PM
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 1 January 5th 05 09:36 AM
Excel 2000 file when opened in Excel 2003 generates errors? Doug Excel Discussion (Misc queries) 13 December 25th 04 11:20 PM
pivotcell object to excel 2000 Kellyc Excel Discussion (Misc queries) 0 December 1st 04 04:45 PM
Statistical Excel Function Question within Excel 2000... Drew H Excel Worksheet Functions 3 October 31st 04 07:55 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017