I have a formula that someone here assisted me with in order to bring in data from multiple sheets.
<code>=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)))))))
</code>
<code>=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)))))))
</code>
=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
<code>=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 <> ""
)
</code>
<code>=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 <> ""
)
</code>
=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!