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

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

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

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

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

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