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. |
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 |
All times are GMT +1. The time now is 09:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com