Search code examples
google-sheets

Getting filter table that searches in row string for dates based on start/end date prompt?


I have a filter table in "Comments Table" sheet. I currently get my data in there through this formula:

=IFNA(UNIQUE(filter({'Master Line List'!C:C,'Master Line List'!F:F,'Master Line List'!I:I,'Master Line List'!G:G,'Master Line List'!A:A},'Master Line List'!I:I=C1,'Master Line List'!G:G=D1)),"")

but now I want it to limit matches based on g2 and h2 start/end date prompts. if they find a match and the text strings in comments column (B) do not contain a date that falls between g2:h2 then do not return a match.

enter image description here

I was helped with a formula in a simpler test sheet, and I tried to incorproate it, but it's not working:

=filter({'Master Line List'!C:C,'Master Line List'!F:F,'Master Line List'!I:I,'Master Line List'!G:G,'Master Line List'!A:A},'Master Line List'!I:I=C1,'Master Line List'!G:G=D1,byrow({'Master Line List'!C:C,'Master Line List'!F:F,'Master Line List'!I:I,'Master Line List'!G:G,'Master Line List'!A:A,lambda(Σ,or(bycol(split(Σ,char(10)),lambda(Λ,isbetween(--left(Λ,find("``",Λ)),G2,H2)))))))

Here is a link to my sheet


Solution

  • You may try:

    =filter(choosecols('Master Line List'!A:I,3,6,9,7,1),'Master Line List'!I:I=C1,'Master Line List'!G:G=D1,
            byrow('Master Line List'!F:F,lambda(Σ,or(bycol(split(Σ,char(10)),lambda(Λ,isbetween(--left(Λ,find(" ",Λ)),G2,H2)))))))
    

    enter image description here