Automatic copying data excluding blank cells
 

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

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

--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---
"Wesley" wrote in message
...
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

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
--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---

Hi Max,

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


-----Original Message-----
You've got 2 responses to your identical post=20
in .public.excel

--=20
Rgds
Max
xl 97
--
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
---
"Wesley" wrote in=20
message
...
I have one column with a formula in multiple rows=20
within the same column.
The formula pulls data from other parts of the=20
spreadsheet or leaves the
cells blank. An example of the formula is =3Dif
(A1=3D0,"",A1).

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

Bob
Mary
"Blank"
Joe
"Blank"
Jill

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

Bob
Mary
Joe
Jill

Any help would be most appreciated.

Thank you,

Wesley



.

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(A 1:A7))),"",INDEX($I$51:$I$
57,MATCH(SMALL(IF(I51:I57<"",ROW(A1:A7)),ROW(A1:A 7)),IF(I51:I57<"",ROW(A1:
A7)),0)))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Wesley" wrote in message
...
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!

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 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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

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

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----