Search code examples
google-sheetsgoogle-sheets-formula

Fill out empty cells in google sheet under a condition

I want to fill out empty cells in column B with the right codes. The rule is that the code must be the same for any email in the same group. Here groups are defined in column A: so each group consists of emails lying between the text "Email" in column A.

This is one example:

Input:

enter image description here

This is the output I want.

enter image description here

Solution

  • EDIT: Fixed formula to return empty if there is no code

    =ARRAYFORMULA(LET(e,A:A,k,B:B,r,ROW(e),c,COUNTIFS(e,"Email",r,"<="&r),
   ARRAY_CONSTRAIN(IF(COUNTIFS(k,"<>",c,c),IF(e="Email",,SCAN(,SORT(k,c,1,1,1),
      LAMBDA(a,c,IF(c="",a,c)))),),MAX(r*({e,k}<>"")),1)))

    enter image description here

    Here's one way to do this (enter this formula in row 1):

    =ARRAYFORMULA(ARRAY_CONSTRAIN(IF(A:A="Email",,
   SCAN(,SORT(B:B,COUNTIFS(A:A,"Email",ROW(A:A),"<="&ROW(A:A)),1,1,1),
     LAMBDA(a,c,IF(c="",a,c)))),MAX(ROW(A:B)*(A:B<>"")),1))

    enter image description here

    Explanation

    We first label each group with:

    =ARRAYFORMULA(COUNTIFS(A:A,"Email",ROW(A:A),"<="&ROW(A:A)))

    enter image description here

    Then we put each code on the top using SORT:

    enter image description here

    After this it's just a simple SCAN fill constrained to the last non-empty row of A:B.

    ARRAY_CONSTRAIN(
  SCAN(,<array_with_codes_on_top>,LAMBDA(a,c,IF(c="",a,c)))),
  MAX(ROW(A:B)*(A:B<>"")),1)