prevent copied blank cells from replacing existing data

[lilleke]

Excel 2000 - this is under help - keyword-null and doesn't work and I need it
to work or am I reading wrong?

I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name

I need to cut/paste fin ofcr name into owner name cell if there is a
financial officer name because I would rather mailer go to fin ofcr. There
isn't always name in fin ofcr col. I scroll until I find fin ofcr name,
cut/paste to col to the left and overwrite the owner's name with fin ofcr
name. about 10 fin ofcr names per visable screen.

Painful on mouse hand.

I want to highlight maybe whole fin ofcr col or at least visable page
cut/paste and overlay col to left with fin ofc name but not make other cells
blank. currently when I do this fin ofcr name is pasted and all other blank
cells are pasted as blanks also.

If copied blank cell overlays not blank cell I want blank cell not copied.
Please help.

 

[Michael]

Place a an autofilter from the Data Menu, then select non blanks
select the columns you want the data from and then hit F5, click on
Special select visible cells only and do your copy and paste if you get an
error stating that the selection already visible cells only, then skip the F5
method select the cells you want and do a copy and paste

 

[bj]

use a helper column in shhet one (c?)
in c1 enter
=if(iserror(vlookup(a1,Sheet2!A:B,2,0),b1,vlookup(A1,sheet2!A:B,2,0))
copy and paste down to the end of your data.
select column C
copy
select column B paste special values, delete column c

 

[lilleke]

Michael - I am probablly missing something - here is what I did
Data/autofilter = never used before - saw the down arrows on col's didn't
touch, right?
I selected non blank cells (5) for test with the control key
Then selected same col I just selected non blanks - right?
F5/special/visible = all ok
you then said to copy and paste - i copied col that I just selected non
blanks/pasted over owner col. It pasted blank cells over owners names, but
did copy over the financial ofcr over. I just need it to not paste blank over
filled cell with name.

what did I do wrong?
Thanking you in advance

 

[lilleke]

bj - thanks for your formula so quickly but Excel 2000 says there was an
error - here is what I did - see what I did wrong, please.

your 1st sentence says 'use a helper col in sheet one (c?)' - my sheet 1 is
the database so i inserted a sheet before it - yes? and entered your formula
in (c) of sheet1 yes/no?

you said copy and past down to the end of your data - do you mean go to
sheet2 and copy the 2 col's with owner name and fin ofcr name? Bring to
sheet1 and paste in col C?

select col C - I get this
copy - I get
select col B paste special values, delete col c - I get.

Please clarify.
thanking you in advance.

 

[lilleke]

please respond to my clarification questions of 082307 thanks

 

[lilleke]

Please respond to my reply of 08.23 - I need clarification - thank you so much

 

[bj]

I misread your initial submission and I missed your earlier response
in the data base if there is anything in Column insert a helper column in
Column C
in C1
enter
=if(B1="",A1,B1)
copy C1 and paste in column C down to the end of your data
Select column c
and copy
select column A and paste special values
delete column C

 

[lilleke]

will try now and let you know.
--
claudia

I misread your initial submission and I missed your earlier response
in the data base if there is anything in Column insert a helper column in
Column C
in C1
enter
=if(B1="",A1,B1)
copy C1 and paste in column C down to the end of your data
Select column c
and copy
select column A and paste special values
delete column C

 

[lilleke]

A1 did not come over to C1 like I understand your formula.
you said if b1 is "" (= blank, right), A1 else B1
B1 does copy over to C1 just fine.

when i copied C over to A special values only the blank lines in C overwrote
the filled cell

i have excel 2000 - i am soooo close
i see no reason for it not to work

 

[lilleke]

GOT IT - THANKS SO SO MUCH - i looked in help and placed a space between ""
and IT WORKED - I AM SO EXCITED THANK YOU.