Use formula to remove blank entries from a column
 

I have a single column of data that is sparse, in that many of the entries
are blank. Since this data is imported from another source, I want to operate
on it with a formula and list the non-blank entries on another page. Thus if
there are 1000 lines of data, but only 20 of them are non-blank, the new
column would have just the 20 non-blank entries listed, with no blank lines
in between.

I don't want to use filters or sorting, since this needs to be an automatic
process once the data is updated. I suspect I can do this with the index
function and treat the column as an array, but I just can't figure out the
right formula.

Any ideas?

Thanks,

Pete

Use formula to remove blank entries from a column
 

Try this:

Data in the range Sheet1 A1:A1000

On Sheet2 enter this formula in A1:

=COUNTA(Sheet1!A:A)

That will return the count of non-empty cells from Sheet1 A1:A1000.

Enter this array formula in A2:

=IF(ROWS(A$2:A2)<=A$1,INDEX(Sheet1!A$1:A$1000,SMAL L(IF(Sheet1!A$1:A$1000<"",ROW(Sheet1!A$1:A$1000)) ,ROWS(A$2:A2))-ROW(Sheet1!A$1)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get blanks. If the number of non-empty cells varies then
you'll have to copy to a range big enough to ensure you get all the expected
results. Only you know how many results you typically expect.

--
Biff
Microsoft Excel MVP


"Pete J" <Pete wrote in message
...
I have a single column of data that is sparse, in that many of the entries
are blank. Since this data is imported from another source, I want to
operate
on it with a formula and list the non-blank entries on another page. Thus
if
there are 1000 lines of data, but only 20 of them are non-blank, the new
column would have just the 20 non-blank entries listed, with no blank
lines
in between.

I don't want to use filters or sorting, since this needs to be an
automatic
process once the data is updated. I suspect I can do this with the index
function and treat the column as an array, but I just can't figure out the
right formula.

Any ideas?

Thanks,

Pete

Use formula to remove blank entries from a column
 

Pete

Bernard posted this a couple hours ago in another thread.

=INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<"",ROW( $A$1:$A$1000)),ROWS($A$1:A1)))

Array formula so use crtl + shift + enter then copy down until you get
error.


Gord Dibben MS Excel MVP


On Thu, 18 Jun 2009 08:48:01 -0700, Pete J <Pete
wrote:

I have a single column of data that is sparse, in that many of the entries
are blank. Since this data is imported from another source, I want to operate
on it with a formula and list the non-blank entries on another page. Thus if
there are 1000 lines of data, but only 20 of them are non-blank, the new
column would have just the 20 non-blank entries listed, with no blank lines
in between.

I don't want to use filters or sorting, since this needs to be an automatic
process once the data is updated. I suspect I can do this with the index
function and treat the column as an array, but I just can't figure out the
right formula.

Any ideas?

Thanks,

Pete

Use formula to remove blank entries from a column
 

You won't accomplish that with just a formula. You can do it easily with a
number of different macros, but they all involve a method you didn't want to
do.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Pete J" wrote:

I have a single column of data that is sparse, in that many of the entries
are blank. Since this data is imported from another source, I want to operate
on it with a formula and list the non-blank entries on another page. Thus if
there are 1000 lines of data, but only 20 of them are non-blank, the new
column would have just the 20 non-blank entries listed, with no blank lines
in between.

I don't want to use filters or sorting, since this needs to be an automatic
process once the data is updated. I suspect I can do this with the index
function and treat the column as an array, but I just can't figure out the
right formula.

Any ideas?

Thanks,

Pete

Use formula to remove blank entries from a column
 

If you dont have duplicate entries; try this..Please note that this is an
array formula. Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula}"

Your data in Col A..
B1
=INDEX(A1:A100,MIN(IF(A1:A100="","",ROW(A1:A100))) )

B2 (all in one line)
=IF(COUNTA($A$1:$A$100)=ROW(),INDEX($A$1:$A$100,M IN(IF(INDIRECT("A" &
MATCH(B1,$A$1:$A$100,0)+1 &":A100")="","",ROW(INDIRECT("A" &
MATCH(B1,$A$1:$A$100,0)+1 & ":A100"))))),"")

and copy that down as required...

If this post helps click Yes
---------------
Jacob Skaria


"Pete J" wrote:

I have a single column of data that is sparse, in that many of the entries
are blank. Since this data is imported from another source, I want to operate
on it with a formula and list the non-blank entries on another page. Thus if
there are 1000 lines of data, but only 20 of them are non-blank, the new
column would have just the 20 non-blank entries listed, with no blank lines
in between.

I don't want to use filters or sorting, since this needs to be an automatic
process once the data is updated. I suspect I can do this with the index
function and treat the column as an array, but I just can't figure out the
right formula.

Any ideas?

Thanks,

Pete

Use formula to remove blank entries from a column
 

Thanks everyone. I tried all the ideas here, and the one that seems to work
best for me is this one. Problem solved!

"T. Valko" wrote:

Try this:

Data in the range Sheet1 A1:A1000

On Sheet2 enter this formula in A1:

=COUNTA(Sheet1!A:A)

That will return the count of non-empty cells from Sheet1 A1:A1000.

Enter this array formula in A2:

=IF(ROWS(A$2:A2)<=A$1,INDEX(Sheet1!A$1:A$1000,SMAL L(IF(Sheet1!A$1:A$1000<"",ROW(Sheet1!A$1:A$1000)) ,ROWS(A$2:A2))-ROW(Sheet1!A$1)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get blanks. If the number of non-empty cells varies then
you'll have to copy to a range big enough to ensure you get all the expected
results. Only you know how many results you typically expect.

--
Biff
Microsoft Excel MVP


"Pete J" <Pete wrote in message
...
I have a single column of data that is sparse, in that many of the entries
are blank. Since this data is imported from another source, I want to
operate
on it with a formula and list the non-blank entries on another page. Thus
if
there are 1000 lines of data, but only 20 of them are non-blank, the new
column would have just the 20 non-blank entries listed, with no blank
lines
in between.

I don't want to use filters or sorting, since this needs to be an
automatic
process once the data is updated. I suspect I can do this with the index
function and treat the column as an array, but I just can't figure out the
right formula.

Any ideas?

Thanks,

Pete

Use formula to remove blank entries from a column
 

John

If I was rating your answer I would give it a low rating.

There are several ways to do this using a formula.


Gord Dibben MS Excel MVP


On Thu, 18 Jun 2009 09:13:09 -0700, John Bundy (remove)
wrote:

You won't accomplish that with just a formula. You can do it easily with a
number of different macros, but they all involve a method you didn't want to
do.

Use formula to remove blank entries from a column
 

It's usually a bad idea to say that something can't be done. I know this
from my own experience! <BG

--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
John

If I was rating your answer I would give it a low rating.

There are several ways to do this using a formula.


Gord Dibben MS Excel MVP


On Thu, 18 Jun 2009 09:13:09 -0700, John Bundy (remove)
wrote:

You won't accomplish that with just a formula. You can do it easily with a
number of different macros, but they all involve a method you didn't want
to
do.

Use formula to remove blank entries from a column
 

Oh yeah!

I know all about that<g

But I don't ask to be rated..........when the readers decide I'm full of
s**t they will let me know by ignoring my posts.

Is that why I don't see many responses?


Gord

On Thu, 18 Jun 2009 14:35:10 -0400, "T. Valko"
wrote:

It's usually a bad idea to say that something can't be done. I know this
from my own experience! <BG