Search code examples
google-sheets

Bringing in only certain data based on start/end date?

I have a formula that someone here assisted me with in order to bring in data from multiple sheets.

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

it works great, but I wanted to take it a step further. G2 and H2 in calc sheet are start/end dates. THis currently pulls in entire comments if any one of those dates in the line breaks contain a date in between G2 and H2.

Is it possible to only bring in the comments that fall within the start/end date and exclude the earlier line breaks?

here is what I attempted, but getting a formula parse error

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

Here is a link to my sheet with the expected result example to the right

any help is greatly appreciated, thank you!

Solution

  • You can re-use the original formula as follows

    =filter({choosecols('Master Line List'!A:I,3),map('Master Line List'!F:F,lambda(c,if(c="","",let(raw,split(c,char(10)),TF,map(raw,lambda(Λ,isbetween(--left(Λ,find(" ",Λ)),G2,H2))),join(char(10),filter(raw,TF)))))),choosecols('Master Line List'!A:I,9,7,1)}, 'Master Line List'!I:I=C1, if(D1="All",'Master Line List'!G:G<>"",'Master Line List'!G:G=D1),
 map('Master Line List'!F:F,lambda(Σ,or(bycol(split(Σ,char(10)),lambda(Λ,isbetween(--left(Λ,find(" ",Λ)),G2,H2)))))))

    with the comment column replaced by

    map('Master Line List'!F:F
   ,lambda(c,if(c=""
               ,""
               ,let(raw
                   ,split(c,char(10))
                   ,TF
                   ,map(raw
                       ,lambda(Λ,isbetween(--left(Λ,find(" ",Λ)),G2,H2))
                   ,join(char(10),filter(raw,TF))))))

    You already know how map(raw,lambda(Λ,isbetween(--left(Λ,find(" ",Λ)),G2,H2)) works, which extracts text value up to a space and then convert it to a number before asking whether it is within date range via isbetween.

    You just need to filter each comment based on that and then join the result per comment in a sensible way, such as delimiting by char(10).

    Since you already had a working formula for a near-identical problem, I will leave it to you to reduce the repeated parts and reorganize as you see fit.

    enter image description here