Automatic copying data excluding blank cells

[Guest]

I have one column with a formula in multiple rows within the same column.
The formula pulls data from other parts of the spreadsheet or leaves the
cells blank. An example of the formula is =if(A1=0,â€â€,A1).

The outcome is to have a column with data in multiple rows mixed with blank
cells like so.

Bob
Mary
“Blankâ€
Joe
“Blankâ€
Jill

What I would like to do is pull the data automatically from this column
putting the data in another column excluding the “Blank†cells like so:

Bob
Mary
Joe
Jill

Any help would be most appreciated.

Thank you,

Wesley

 

[Max]

You've got 2 responses to your identical post in .public.excel

 

[Max]

Here's the response given earlier:

One way ..

Assuming the col below is in B1:B100
(which are returns by formula)

Bob
Mary
"Blank"
Joe
"Blank"
Jill

etc

Select C1:C100

Put in the formula bar:

=IF(ISERROR(SMALL(IF(B1:B100<>"",ROW(B1:B100)),ROW())),"",INDEX(B:B,MATCH(SM
ALL(IF(B1:B100<>"",ROW(B1:B100)),ROW()),IF(B1:B100<>"",ROW(B1:B100)),0)))

Array-enter with CTRL+SHIFT+ENTER
instead of just pressing ENTER

For the sample above,
you'll get the desired results in C1:C100:

Bob
Mary
Joe
Jill
<Rest of the range are "blanks">

Adapt to suit

 

[Wesley]

Hi Max,

thanks - the formula is just what i needed. However for
some reason I can't get the array to work when i copy it
into the actual worksheet that i'm using. I've changed the
formula cell's from B1:B100 to where I need it to search
(column I51:I57) and array-enter - however it just stays
blank?! i'm stumped - do you know why this might be?
Thanks!

 

[Max]

Try this slight mod ..

Your target range is I51:I57

Select an adjacent range, say J51:J57

Put in the formula bar and array-enter:

=IF(ISERROR(SMALL(IF(I51:I57<>"",ROW(A1:A7)),ROW(A1:A7))),"",INDEX($I$51:$I$
57,MATCH(SMALL(IF(I51:I57<>"",ROW(A1:A7)),ROW(A1:A7)),IF(I51:I57<>"",ROW(A1:
A7)),0)))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Hi Max,

thanks - the formula is just what i needed. However for
some reason I can't get the array to work when i copy it
into the actual worksheet that i'm using. I've changed the
formula cell's from B1:B100 to where I need it to search
(column I51:I57) and array-enter - however it just stays
blank?! i'm stumped - do you know why this might be?
Thanks!

 

[Max]

Came across Aladin's response below in Excelforum
(Think it didn't propagate over ..)

See for a different (efficient) approach:
http://www.mrexcel.com/board2/viewt...2601&highlight=

For your info ..

 

[Max]

Sorry, think the link originally posted by Aladin
didn't carry through too well in the previous post

Try instead: http://tinyurl.com/3s2vm
which should bring you there