Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminating Blank Values
I have a list in Column A of 100 values, from cell A1 to A10. Several of the
cells are empty (=" "). How can I arrange Column B so that it includes in the same order all the values of Column A, but excluding the empty cells? -- Ken |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminating Blank Values
In B1 enter:
=IF(ROWS($1:1)<=COUNTA($A$1:$A$100),INDEX($A$1:$A$ 100,SMALL(IF($A$1:$A$100<"",ROW($A$1:$A$100)-MIN(ROW($A$1:$A$100))+1),ROWS($1:1))),"") This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. Then copy B1 and paste down as far you you need. -- Gary''s Student - gsnu200812 "Ken" wrote: I have a list in Column A of 100 values, from cell A1 to A10. Several of the cells are empty (=" "). How can I arrange Column B so that it includes in the same order all the values of Column A, but excluding the empty cells? -- Ken |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminating Blank Values
Hi,
I think this does what you want: =INDEX(A$1:A$100,SMALL(IF(ROW(A$1:A$100)*(A$1:A$10 0<"")0,ROW(A$1:A$100),""),ROW(A1))) This formula wil return #NUM errors after it has found all items. You can enhance the formula to avoid this: =IF(ROW(A1)COUNT(A$1:A$100),"",INDEX(A$1:A$100,SM ALL(IF(ROW(A$1:A$100)*(A$1:A$100<"")0,ROW(A$1:A$ 100),""),ROW(A1)))) If the formulas are going to start on the same row you can simplify this to: =IF(ROW()COUNT(A$1:A$100),"",INDEX(A$1:A$100,SMAL L(IF(ROW(A$1:A$100)*(A$1:A$100<"")0,ROW(A$1:A$10 0),""),ROW()))) If this helps, please click the Yes button -- Thanks, Shane Devenshire "Ken" wrote: I have a list in Column A of 100 values, from cell A1 to A10. Several of the cells are empty (=" "). How can I arrange Column B so that it includes in the same order all the values of Column A, but excluding the empty cells? -- Ken -- Thanks, Shane Devenshire "Ken" wrote: I have a list in Column A of 100 values, from cell A1 to A10. Several of the cells are empty (=" "). How can I arrange Column B so that it includes in the same order all the values of Column A, but excluding the empty cells? -- Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003-eliminating blank rows in target worksheet | Excel Discussion (Misc queries) | |||
Eliminating Blank Rows | Excel Discussion (Misc queries) | |||
Sorting and Eliminating Blank Cells in Formula range | Excel Worksheet Functions | |||
Eliminating blank cells in a list on a ROW | Excel Worksheet Functions | |||
Eliminating Blank Cells From Lists on different worksheets | Excel Discussion (Misc queries) |