CODEWITHSUNDEEP
regexif-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas
Apex_rookie
Apex_rookie

Reputation: 43

Need to apply formula to certain blank cells

I have a formula here where it is supposed to only activate if a cell in the B column is blank and when a cell in the A column is not blank. When it does activate it takes the value in A and puts it in the blank B cell. here is the code:

 =IF(AND(not(isblank(A)),isblank(B)), B, A)

the problem I'm seeing is that I want this formula to be put on certain blank cells in the B column. is there a way for me to select all of those blanks in the B column and then add the formula to all of them at once so it doesn't mess with the cells that already have values in the B column?

Upvotes: 4

Views: 537

Answers (2)

user11982798
user11982798

Reputation: 1908

a. For Non Blank A, get A for blank B and get B for non Blank B (Result1):

= filter
  ( 
     IF 
     ( 
        not(isblank(A2:A)), if(isblank(B2:B),A2:A,B2:B),""
     ) 
     , max(filter(row($A$2:$B) ,$A$2:A<>""))>=row($A$2:$B)
  )

b. For Non Blank A, Just get A for blank B (Result2):

= filter
  ( 
     IF 
     ( 
        not(isblank(A2:A)), if(isblank(B2:B),A2:A,""),""
     ) 
     , max(filter(row($A$2:$B) ,$A$2:A<>""))>=row($A$2:$B)
  )

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(REGEXMATCH(TO_TEXT(ROW(A3:A)), "4|12|13"), 
 IF((A3:A<>"")*(B3:B=""), B3:B, A3:A), ))

where row 11 was skipped

0

or inverted:

=ARRAYFORMULA(IF(NOT(REGEXMATCH(TO_TEXT(ROW(A3:A)), "11")), 
 IF((A3:A<>"")*(B3:B=""), B3:B, A3:A), ))

0

Upvotes: 1

Related Questions