3

Other questions have touched on this and offered solutions that are not viable for a very large data set. I have a formula like the following across 9 columns:

=IF(A1=A2, B2, "zz")

I then autofill about 3.5 million cells + copy -> paste values. Then I find and replace "zz" with "empty cell". However, finding and replacing a million or so "zz" strings is a very slow process. I'd rather not write anything there in the first place. So my question is, how to I write the following formula:

=IF(A1=A2, B2, 'leave cell alone and write nothing there at all')

Excel seems to be incapable of not touching a cell in case of FALSE (or TRUE for that matter).

ZygD
  • 8,011
  • 21
  • 49
  • 67
Simon de Vegt
  • 31
  • 1
  • 1
  • 2
  • 1
    Why not just `if(a1=a2,b2,'')`? – Matt Cremeens May 18 '15 at 23:44
  • If you're really hung up on blank string vs empty cell, then I'd suggest writing a macro which simply skips writing to cells when your condition isn't met. If you turn off screen refreshing, its probably going to be a lot faster when processing 3.5M cells. – DeanOC May 18 '15 at 23:46
  • `=IF(A1=A2, B2, "")` is what you are looking for – chancea May 18 '15 at 23:47
  • 2
    Can you step back and explain why an empty string as in `=IF(A1=A2, B2, "")` will not work in your case? – teylyn May 18 '15 at 23:52
  • 1
    Here you go! Get a true empty blank if condition is met: https://stackoverflow.com/a/39351425/1903793 – Przemyslaw Remin Nov 07 '17 at 11:06

3 Answers3

5

The fact that the cell contains a formula already means that it is not truly empty. Common practice is to use an empty string, like

=IF(A1=A2, B2,"")

There are not many situations where this will be problematic, and if a problem arises, in most cases there are different techniques that can be applied.

teylyn
  • 30,863
  • 3
  • 48
  • 62
1

Let NULL be the name of a cell that has "" in it, and BLANK be the name of a cell that is truly blank.

NULL is not truly blank; ISBLANK(NULL) will return FALSE. But note that '=' takes blank and null cells to be identical. So for any cell C1:

(C1="")
(C1=BLANK)
(C1=NULL)

will all return TRUE if C1 is truly blank OR if it contains "". (I find this simpler than using COUNTBLANK(C1)>0, which has been suggested elsewhere.)

Simpliciter
  • 11
  • 1
-1

try this

=IF(A1=A2,B2,TRIM(""))
skypjack
  • 45,296
  • 16
  • 80
  • 161
Shahim Khlaifat
  • 63
  • 1
  • 1
  • 8