Condensing a list/range with blank cells to a new list/range without blanks

Have you considered using Autofilter and filtering for Blanks?


"KR" wrote:

> I have a list of cells, which are all dynamically linked to other stuff in
> my workbook. The main purpose is that the non-blank cells are "flags" that
> show that some task has not been completed. However, I have a long enough
> list that it isn't easy to look at, or print, because each cell is dedicated
> to one task.
>
> What I'd like to do is set a new range of cells to show only the "non-blank"
> cells from the larger range.
>
> I did put all my source cells in one column.
>
> Can anyone give me an easy formula that will check for each subsequent
> non-blank cell? I've been playing around with VLookup, but haven't gotten it
> working yet. It is also possible that the first (or first several) cells in
> my larger range might be blank, so that makes it a bit harder.
>
> Any help or code snippets would be greatly appreciated!
>
> Thanks,
> Keith
>
> --
> The enclosed questions or comments are entirely mine and don't represent the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>
>

I like using <data><filters><Autofilter> for this type of operation.
Select the column with your flags
use autofilter
select "non-blanks"
this will get all of your non blank data together.
note:this is not a dynamic action. You have ot reselect the non blanks when
things have changed to get an updated list.


"KR" wrote:

> I have a list of cells, which are all dynamically linked to other stuff in
> my workbook. The main purpose is that the non-blank cells are "flags" that
> show that some task has not been completed. However, I have a long enough
> list that it isn't easy to look at, or print, because each cell is dedicated
> to one task.
>
> What I'd like to do is set a new range of cells to show only the "non-blank"
> cells from the larger range.
>
> I did put all my source cells in one column.
>
> Can anyone give me an easy formula that will check for each subsequent
> non-blank cell? I've been playing around with VLookup, but haven't gotten it
> working yet. It is also possible that the first (or first several) cells in
> my larger range might be blank, so that makes it a bit harder.
>
> Any help or code snippets would be greatly appreciated!
>
> Thanks,
> Keith
>
> --
> The enclosed questions or comments are entirely mine and don't represent the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>
>

Auto filter i guess is the best route but if need a formula here is one:-
Assuming your list is on cell A1:a1000 On cell B1 type this formula below
and copy all the waydown to b1000
=IF(ISERROR(INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000)),ROW(1:1)))),"",INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000)),ROW(1:1))))
array entered(ctrl+shift+enter)





"KR" <[email protected]> wrote in message
news:[email protected]...
>I have a list of cells, which are all dynamically linked to other stuff in
> my workbook. The main purpose is that the non-blank cells are "flags" that
> show that some task has not been completed. However, I have a long enough
> list that it isn't easy to look at, or print, because each cell is
> dedicated
> to one task.
>
> What I'd like to do is set a new range of cells to show only the
> "non-blank"
> cells from the larger range.
>
> I did put all my source cells in one column.
>
> Can anyone give me an easy formula that will check for each subsequent
> non-blank cell? I've been playing around with VLookup, but haven't gotten
> it
> working yet. It is also possible that the first (or first several) cells
> in
> my larger range might be blank, so that makes it a bit harder.
>
> Any help or code snippets would be greatly appreciated!
>
> Thanks,
> Keith
>
> --
> The enclosed questions or comments are entirely mine and don't represent
> the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>

Beautiful, this is exactly what I needed- Thanks!!!!!

"N Harkawat" <nharkawat@hotmail_dot_com> wrote in message
news:[email protected]...
> Auto filter i guess is the best route but if need a formula here is one:-
> Assuming your list is on cell A1:a1000 On cell B1 type this formula below
> and copy all the waydown to b1000
>
=IF(ISERROR(INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000)),
ROW(1:1)))),"",INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000
)),ROW(1:1))))
> array entered(ctrl+shift+enter)
>
>
>
>
>
> "KR" <[email protected]> wrote in message
> news:[email protected]...
> >I have a list of cells, which are all dynamically linked to other stuff
in
> > my workbook. The main purpose is that the non-blank cells are "flags"
that
> > show that some task has not been completed. However, I have a long
enough
> > list that it isn't easy to look at, or print, because each cell is
> > dedicated
> > to one task.
> >
> > What I'd like to do is set a new range of cells to show only the
> > "non-blank"
> > cells from the larger range.
> >
> > I did put all my source cells in one column.
> >
> > Can anyone give me an easy formula that will check for each subsequent
> > non-blank cell? I've been playing around with VLookup, but haven't
gotten
> > it
> > working yet. It is also possible that the first (or first several) cells
> > in
> > my larger range might be blank, so that makes it a bit harder.
> >
> > Any help or code snippets would be greatly appreciated!
> >
> > Thanks,
> > Keith
> >
> > --
> > The enclosed questions or comments are entirely mine and don't represent
> > the
> > thoughts, views, or policy of my employer. Any errors or omissions are
my
> > own.
> >
> >
>
>

Have you considered using Autofilter and filtering for Blanks?


"KR" wrote:

> I have a list of cells, which are all dynamically linked to other stuff in
> my workbook. The main purpose is that the non-blank cells are "flags" that
> show that some task has not been completed. However, I have a long enough
> list that it isn't easy to look at, or print, because each cell is dedicated
> to one task.
>
> What I'd like to do is set a new range of cells to show only the "non-blank"
> cells from the larger range.
>
> I did put all my source cells in one column.
>
> Can anyone give me an easy formula that will check for each subsequent
> non-blank cell? I've been playing around with VLookup, but haven't gotten it
> working yet. It is also possible that the first (or first several) cells in
> my larger range might be blank, so that makes it a bit harder.
>
> Any help or code snippets would be greatly appreciated!
>
> Thanks,
> Keith
>
> --
> The enclosed questions or comments are entirely mine and don't represent the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>
>

I like using <data><filters><Autofilter> for this type of operation.
Select the column with your flags
use autofilter
select "non-blanks"
this will get all of your non blank data together.
note:this is not a dynamic action. You have ot reselect the non blanks when
things have changed to get an updated list.


"KR" wrote:

> I have a list of cells, which are all dynamically linked to other stuff in
> my workbook. The main purpose is that the non-blank cells are "flags" that
> show that some task has not been completed. However, I have a long enough
> list that it isn't easy to look at, or print, because each cell is dedicated
> to one task.
>
> What I'd like to do is set a new range of cells to show only the "non-blank"
> cells from the larger range.
>
> I did put all my source cells in one column.
>
> Can anyone give me an easy formula that will check for each subsequent
> non-blank cell? I've been playing around with VLookup, but haven't gotten it
> working yet. It is also possible that the first (or first several) cells in
> my larger range might be blank, so that makes it a bit harder.
>
> Any help or code snippets would be greatly appreciated!
>
> Thanks,
> Keith
>
> --
> The enclosed questions or comments are entirely mine and don't represent the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>
>

Auto filter i guess is the best route but if need a formula here is one:-
Assuming your list is on cell A1:a1000 On cell B1 type this formula below
and copy all the waydown to b1000
=IF(ISERROR(INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000)),ROW(1:1)))),"",INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000)),ROW(1:1))))
array entered(ctrl+shift+enter)





"KR" <[email protected]> wrote in message
news:[email protected]...
>I have a list of cells, which are all dynamically linked to other stuff in
> my workbook. The main purpose is that the non-blank cells are "flags" that
> show that some task has not been completed. However, I have a long enough
> list that it isn't easy to look at, or print, because each cell is
> dedicated
> to one task.
>
> What I'd like to do is set a new range of cells to show only the
> "non-blank"
> cells from the larger range.
>
> I did put all my source cells in one column.
>
> Can anyone give me an easy formula that will check for each subsequent
> non-blank cell? I've been playing around with VLookup, but haven't gotten
> it
> working yet. It is also possible that the first (or first several) cells
> in
> my larger range might be blank, so that makes it a bit harder.
>
> Any help or code snippets would be greatly appreciated!
>
> Thanks,
> Keith
>
> --
> The enclosed questions or comments are entirely mine and don't represent
> the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>

Beautiful, this is exactly what I needed- Thanks!!!!!

"N Harkawat" <nharkawat@hotmail_dot_com> wrote in message
news:[email protected]...
> Auto filter i guess is the best route but if need a formula here is one:-
> Assuming your list is on cell A1:a1000 On cell B1 type this formula below
> and copy all the waydown to b1000
>
=IF(ISERROR(INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000)),
ROW(1:1)))),"",INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000
)),ROW(1:1))))
> array entered(ctrl+shift+enter)
>
>
>
>
>
> "KR" <[email protected]> wrote in message
> news:[email protected]...
> >I have a list of cells, which are all dynamically linked to other stuff
in
> > my workbook. The main purpose is that the non-blank cells are "flags"
that
> > show that some task has not been completed. However, I have a long
enough
> > list that it isn't easy to look at, or print, because each cell is
> > dedicated
> > to one task.
> >
> > What I'd like to do is set a new range of cells to show only the
> > "non-blank"
> > cells from the larger range.
> >
> > I did put all my source cells in one column.
> >
> > Can anyone give me an easy formula that will check for each subsequent
> > non-blank cell? I've been playing around with VLookup, but haven't
gotten
> > it
> > working yet. It is also possible that the first (or first several) cells
> > in
> > my larger range might be blank, so that makes it a bit harder.
> >
> > Any help or code snippets would be greatly appreciated!
> >
> > Thanks,
> > Keith
> >
> > --
> > The enclosed questions or comments are entirely mine and don't represent
> > the
> > thoughts, views, or policy of my employer. Any errors or omissions are
my
> > own.
> >
> >
>
>

I have a list of cells, which are all dynamically linked to other stuff in
my workbook. The main purpose is that the non-blank cells are "flags" that
show that some task has not been completed. However, I have a long enough
list that it isn't easy to look at, or print, because each cell is dedicated
to one task.

What I'd like to do is set a new range of cells to show only the "non-blank"
cells from the larger range.

I did put all my source cells in one column.

Can anyone give me an easy formula that will check for each subsequent
non-blank cell? I've been playing around with VLookup, but haven't gotten it
working yet. It is also possible that the first (or first several) cells in
my larger range might be blank, so that makes it a bit harder.

Any help or code snippets would be greatly appreciated!

Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Have you considered using Autofilter and filtering for Blanks?


"KR" wrote:

> I have a list of cells, which are all dynamically linked to other stuff in
> my workbook. The main purpose is that the non-blank cells are "flags" that
> show that some task has not been completed. However, I have a long enough
> list that it isn't easy to look at, or print, because each cell is dedicated
> to one task.
>
> What I'd like to do is set a new range of cells to show only the "non-blank"
> cells from the larger range.
>
> I did put all my source cells in one column.
>
> Can anyone give me an easy formula that will check for each subsequent
> non-blank cell? I've been playing around with VLookup, but haven't gotten it
> working yet. It is also possible that the first (or first several) cells in
> my larger range might be blank, so that makes it a bit harder.
>
> Any help or code snippets would be greatly appreciated!
>
> Thanks,
> Keith
>
> --
> The enclosed questions or comments are entirely mine and don't represent the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>
>

I like using <data><filters><Autofilter> for this type of operation.
Select the column with your flags
use autofilter
select "non-blanks"
this will get all of your non blank data together.
note:this is not a dynamic action. You have ot reselect the non blanks when
things have changed to get an updated list.


"KR" wrote:

> I have a list of cells, which are all dynamically linked to other stuff in
> my workbook. The main purpose is that the non-blank cells are "flags" that
> show that some task has not been completed. However, I have a long enough
> list that it isn't easy to look at, or print, because each cell is dedicated
> to one task.
>
> What I'd like to do is set a new range of cells to show only the "non-blank"
> cells from the larger range.
>
> I did put all my source cells in one column.
>
> Can anyone give me an easy formula that will check for each subsequent
> non-blank cell? I've been playing around with VLookup, but haven't gotten it
> working yet. It is also possible that the first (or first several) cells in
> my larger range might be blank, so that makes it a bit harder.
>
> Any help or code snippets would be greatly appreciated!
>
> Thanks,
> Keith
>
> --
> The enclosed questions or comments are entirely mine and don't represent the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>
>

Auto filter i guess is the best route but if need a formula here is one:-
Assuming your list is on cell A1:a1000 On cell B1 type this formula below
and copy all the waydown to b1000
=IF(ISERROR(INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000)),ROW(1:1)))),"",INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000)),ROW(1:1))))
array entered(ctrl+shift+enter)





"KR" <[email protected]> wrote in message
news:[email protected]...
>I have a list of cells, which are all dynamically linked to other stuff in
> my workbook. The main purpose is that the non-blank cells are "flags" that
> show that some task has not been completed. However, I have a long enough
> list that it isn't easy to look at, or print, because each cell is
> dedicated
> to one task.
>
> What I'd like to do is set a new range of cells to show only the
> "non-blank"
> cells from the larger range.
>
> I did put all my source cells in one column.
>
> Can anyone give me an easy formula that will check for each subsequent
> non-blank cell? I've been playing around with VLookup, but haven't gotten
> it
> working yet. It is also possible that the first (or first several) cells
> in
> my larger range might be blank, so that makes it a bit harder.
>
> Any help or code snippets would be greatly appreciated!
>
> Thanks,
> Keith
>
> --
> The enclosed questions or comments are entirely mine and don't represent
> the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>

Beautiful, this is exactly what I needed- Thanks!!!!!

"N Harkawat" <nharkawat@hotmail_dot_com> wrote in message
news:[email protected]...
> Auto filter i guess is the best route but if need a formula here is one:-
> Assuming your list is on cell A1:a1000 On cell B1 type this formula below
> and copy all the waydown to b1000
>
=IF(ISERROR(INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000)),
ROW(1:1)))),"",INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000
)),ROW(1:1))))
> array entered(ctrl+shift+enter)
>
>
>
>
>
> "KR" <[email protected]> wrote in message
> news:[email protected]...
> >I have a list of cells, which are all dynamically linked to other stuff
in
> > my workbook. The main purpose is that the non-blank cells are "flags"
that
> > show that some task has not been completed. However, I have a long
enough
> > list that it isn't easy to look at, or print, because each cell is
> > dedicated
> > to one task.
> >
> > What I'd like to do is set a new range of cells to show only the
> > "non-blank"
> > cells from the larger range.
> >
> > I did put all my source cells in one column.
> >
> > Can anyone give me an easy formula that will check for each subsequent
> > non-blank cell? I've been playing around with VLookup, but haven't
gotten
> > it
> > working yet. It is also possible that the first (or first several) cells
> > in
> > my larger range might be blank, so that makes it a bit harder.
> >
> > Any help or code snippets would be greatly appreciated!
> >
> > Thanks,
> > Keith
> >
> > --
> > The enclosed questions or comments are entirely mine and don't represent
> > the
> > thoughts, views, or policy of my employer. Any errors or omissions are
my
> > own.
> >
> >
>
>