Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating blanks
Hi,
Is there a function i could use please to reduce the size of a list. I have a list of names in col A, with values in B etc, in between certain cells may be a range of blank cells. So if i start with a list such as Bill 2 (blank) Fred 3 Dave 1 (blank) (blank) Wilma 3 The output I would like to generate is Bill 2 Fred 3 Dave 1 Wilma 3 Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating blanks
Select the range. Press F5. From Goto window select blanks. OK Right click
delete cellsEntire Row..OK If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Hi, Is there a function i could use please to reduce the size of a list. I have a list of names in col A, with values in B etc, in between certain cells may be a range of blank cells. So if i start with a list such as Bill 2 (blank) Fred 3 Dave 1 (blank) (blank) Wilma 3 The output I would like to generate is Bill 2 Fred 3 Dave 1 Wilma 3 Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating blanks
Yes,
I am looking for a formula that could do it on auto actually. I have tried several formulas but this one is a bit past me. Do you know is it possible with a formula? Thanks "Jacob Skaria" wrote: Select the range. Press F5. From Goto window select blanks. OK Right click delete cellsEntire Row..OK If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Hi, Is there a function i could use please to reduce the size of a list. I have a list of names in col A, with values in B etc, in between certain cells may be a range of blank cells. So if i start with a list such as Bill 2 (blank) Fred 3 Dave 1 (blank) (blank) Wilma 3 The output I would like to generate is Bill 2 Fred 3 Dave 1 Wilma 3 Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating blanks
**Array formula** You have to press ctrl+shift+enter, not just enter
XL2007 =IFERROR(INDEX(NameList,SMALL(IF(NameList<"",ROW( INDIRECT("1:"&ROWS(NameList)))),ROWS($1:1))),"") copy down as far as needed All version =IF(COUNTA(NameList)=ROWS($1:1),INDEX(NameList,SM ALL(IF(NameList<"",ROW(INDIRECT("1:"&ROWS(NameLis t)))),ROWS($1:1))),"") copy down as far as needed "LiAD" wrote: Yes, I am looking for a formula that could do it on auto actually. I have tried several formulas but this one is a bit past me. Do you know is it possible with a formula? Thanks "Jacob Skaria" wrote: Select the range. Press F5. From Goto window select blanks. OK Right click delete cellsEntire Row..OK If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Hi, Is there a function i could use please to reduce the size of a list. I have a list of names in col A, with values in B etc, in between certain cells may be a range of blank cells. So if i start with a list such as Bill 2 (blank) Fred 3 Dave 1 (blank) (blank) Wilma 3 The output I would like to generate is Bill 2 Fred 3 Dave 1 Wilma 3 Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating blanks
If both start in row 1, you can use this formula in another cell in row 1: array enter (enter using
Ctrl-Shift-Enter) =IF(COUNTIF($A$1:$A$12,"<")=ROW(),INDEX($A$1:$A$ 12, LARGE(($A$1:$A$12<"") *(ROW($A$1:$A$12)),COUNTIF($A$1:$A$12,"<")-ROW()+ROW($B$1))),"") and next to it, also in row 1 =IF(COUNTIF($A$1:$A$12,"<")=ROW(),INDEX($B$1:$B$ 12, LARGE(($A$1:$A$12<"") *(ROW($A$1:$A$12)),COUNTIF($A$1:$A$12,"<")-ROW()+ROW($B$1))),"") If your list or formula doesn't start in row 1 then you need to adjust the INDEX formula to account for that... =IF(COUNTIF($A$YYYY:$A$14,"<")=ROW(A1),INDEX($A$ YYYY:$A$14, LARGE(($A$YYYY:$A$14<"") *(ROW($A$YYYY:$A$14)-ROW($A$(YYYY-1))),COUNTIF($A$YYYY:$A$14,"<")-ROW()+ROW($B$XXXX))),"") where XXXX is the row where you are entering the formula, and YYYY is the first row of your list- and you need to change ROW($A$(YYYY-1)) to the cell one row above your list, like ROW($A$4) HTH, Bernie MS Excel MVP "LiAD" wrote in message ... Hi, Is there a function i could use please to reduce the size of a list. I have a list of names in col A, with values in B etc, in between certain cells may be a range of blank cells. So if i start with a list such as Bill 2 (blank) Fred 3 Dave 1 (blank) (blank) Wilma 3 The output I would like to generate is Bill 2 Fred 3 Dave 1 Wilma 3 Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating blanks
Hi,
1. Formulas don't take action in the spreadsheet, they only return results! 2. Sort your data based on colums A and B. Blanks go to the bottom. 3. You could write code to remove the blank rows but #2 is simplier. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "LiAD" wrote: Yes, I am looking for a formula that could do it on auto actually. I have tried several formulas but this one is a bit past me. Do you know is it possible with a formula? Thanks "Jacob Skaria" wrote: Select the range. Press F5. From Goto window select blanks. OK Right click delete cellsEntire Row..OK If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Hi, Is there a function i could use please to reduce the size of a list. I have a list of names in col A, with values in B etc, in between certain cells may be a range of blank cells. So if i start with a list such as Bill 2 (blank) Fred 3 Dave 1 (blank) (blank) Wilma 3 The output I would like to generate is Bill 2 Fred 3 Dave 1 Wilma 3 Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blanks non blanks in filter | Excel Discussion (Misc queries) | |||
Eliminating Blanks in a Summary Sheet | Excel Discussion (Misc queries) | |||
Sumproduct copying blanks or how to insert zero into blanks | Excel Worksheet Functions | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |