Forum Discussion

jaolvera's avatar
jaolvera
Brass Contributor
Jul 14, 2023
Solved

Formula assistance

Hello,  I was wondering if there was formula I could use that would essentially list out any person where the calREDIE DI# column is blank (Column Au would blank), and fill each row (Photo below tha...
Formulas and Functions
  • flexyourdata's avatar
    flexyourdata
    Jul 18, 2023

    jaolvera 

     

    Yes, edit the second argument to FILTER:

     

    =LET(
    missing, FILTER(Linelist!B2:D4, (Linelist!AU2:AU4 = "")*(LineList!B2:B4<>"")),
    TAKE(missing, , 1) & ", " & CHOOSECOLS(missing, 2) & " " &
        TEXT(TAKE(missing, , -1), "mm/dd/yyyy")
)
jaolvera's avatar
jaolvera
Brass Contributor
Jul 18, 2023

Riny_van_Eekelenhello! the formula worked! but is there anyway for the data to be filled in each line versus all in one cell? 

I.e each person who has a missing ID will be listed in each cell

 

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jul 18, 2023

jaolvera Please upload a file with some fake data. Perhaps I just misunderstood or couldn't force what you are dealing with.

  • jaolvera's avatar
    jaolvera
    Brass Contributor
    Jul 18, 2023
    Thank you so much!!!! worked perfectly
  • flexyourdata's avatar
    flexyourdata
    Iron Contributor
    Jul 18, 2023

    jaolvera 

     

    Yes, edit the second argument to FILTER:

     

    =LET(
    missing, FILTER(Linelist!B2:D4, (Linelist!AU2:AU4 = "")*(LineList!B2:B4<>"")),
    TAKE(missing, , 1) & ", " & CHOOSECOLS(missing, 2) & " " &
        TEXT(TAKE(missing, , -1), "mm/dd/yyyy")
)
  • jaolvera's avatar
    jaolvera
    Brass Contributor
    Jul 18, 2023
    thank you! that worked, if there a way to add an "if" statement that will apply this formula only if Column b has data entered?
  • flexyourdata's avatar
    flexyourdata
    Iron Contributor
    Jul 18, 2023

    jaolvera 

     

    You had the spill error from my original formula because your input area for the formula is comprised of merged cells. You should un-merge the white cells for columns R and S, then enter the formula:

     

    =LET(
    missing, FILTER(Linelist!B2:D4, Linelist!AU2:AU4 = ""),
    TAKE(missing, , 1) & ", " & CHOOSECOLS(missing, 2) & " " &
        TEXT(TAKE(missing, , -1), "mm/dd/yyyy")
)

     

     

     

     

  • flexyourdata's avatar
    flexyourdata
    Iron Contributor
    Jul 18, 2023

    Riny_van_Eekelen 

     

    jaolvera 

     

    The issue is that TEXTJOIN doesn't join row-wise. So, you either need to wrap it in BYROW and use TEXTJOIN row-wise or revert to the simple column concatenation as in my original formula.