Forum Discussion
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...
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 If you have Excel365 you could try something like this:
=BYROW(FILTER(B2:D5,AU2:AU5<>""),LAMBDA(a,TEXTJOIN(",",,a)))
flexyourdata I tried applying the formula that you used on my last question with this one, since its the same scenario but I couldnt get it to work, ar you able to assist?
Try a variant of this (adjust the ranges):
=LET( missing, FILTER(B2:D5, AU2:AU5 = ""), TAKE(missing, , 1) & ", " & CHOOSECOLS(missing, 2) & " " & TEXT(TAKE(missing, , -1), "mm/dd/yyyy") )jaolvera Do you have Excel365? Can you upload/share a file?
- I added the attachment
so workbook 1 is where the formula would go and the cells its refrencing would be "linelist" workbook.
the formula you provided I have already in the workbook when you click on the celljaolvera I misread your question and now see that you want info on the missing IDs. My initial formula took ID's that were not missing. Then you should also check that e.g. the Last Name is not empty, otherwise the formula will result in a spill error. And I indeed forgot to properly format the Birth Date in the TEXTJOIN function. Inspired by flexyourdata , change the formula in S12 as mentioned below and it shall work in your Dashboard.
=LET( missing, FILTER( Linelist!B:D, (Linelist!B:B <> "") * (Linelist!AU:AU = "") ), TEXTJOIN( ", ", , DROP(missing, , -1), TEXT(TAKE(missing, , -1), "mm/dd/yyyy") ) )
- How an I share the file?