Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Wesley
 
Posts: n/a
Default 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

  #2   Report Post  
Max
 
Posts: n/a
Default

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



  #3   Report Post  
Max
 
Posts: n/a
Default

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
---


  #4   Report Post  
Wesley
 
Posts: n/a
Default

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



.

  #5   Report Post  
Max
 
Posts: n/a
Default

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!




  #6   Report Post  
Max
 
Posts: n/a
Default

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
----


  #7   Report Post  
Max
 
Posts: n/a
Default

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
----
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I copy data (word) into respective cells when the data bei. awg9tech New Users to Excel 1 January 12th 05 12:26 PM
How would I fill blank cells with the data from a previous cell? Clive Darling Excel Discussion (Misc queries) 3 January 6th 05 02:10 AM
To safety merge cells without data destroyed, and smart unmerge! Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:17 AM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
Automatic copying of data cells DaleM Excel Discussion (Misc queries) 2 December 29th 04 08:07 PM


All times are GMT +1. The time now is 05:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"