Filter Function: Include using Spill Range

[ChiefLapuLapu]

Hello,

In cell N2 I have the following formula = TRANSPOSE(UNIQUE(FILTER(Calendar[Week-Year],(Calendar[Date]>=L2)*(Calendar[Date]<=M2))))

This works, but I would like to use a spill range for arguments L2 and M2. Both columns L & M are separate spill range formulas.

I tried the following formula, but receive an #N/A error in cell N2.

=TRANSPOSE(UNIQUE(FILTER(Calendar[Week-Year],(Calendar[Date]>=L2#)*(Calendar[Date]<=M2#)))).

I imagine I will have to utilize LAMBDA, but having trouble figuring that part out.

Thanks in advance.

 

[Herakles]

Hello,

In cell N2 I have the following formula = TRANSPOSE(UNIQUE(FILTER(Calendar[Week-Year],(Calendar[Date]>=L2)*(Calendar[Date]<=M2))))

View attachment 99500

This works, but I would like to use a spill range for arguments L2 and M2. Both columns L & M are separate spill range formulas.

I tried the following formula, but receive an #N/A error in cell N2.

=TRANSPOSE(UNIQUE(FILTER(Calendar[Week-Year],(Calendar[Date]>=L2#)*(Calendar[Date]<=M2#)))).

I imagine I will have to utilize LAMBDA, but having trouble figuring that part out.

Thanks in advance.

Can you please send us the source data and this analysis using XL2BB?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Fluff]

How about

=DROP(REDUCE("",SEQUENCE(ROWS(L2#)),LAMBDA(x,y,VSTACK(x,TOROW(UNIQUE(FILTER(Calendar[Week-Year],(Calendar[Date]>=INDEX(L2#,y,1))*(Calendar[Date]<=INDEX(M2#,y,1)))))))),1)

 

[ChiefLapuLapu]

How about

=DROP(REDUCE("",SEQUENCE(ROWS(L2#)),LAMBDA(x,y,VSTACK(x,TOROW(UNIQUE(FILTER(Calendar[Week-Year],(Calendar[Date]>=INDEX(L2#,y,1))*(Calendar[Date]<=INDEX(M2#,y,1)))))))),1)

Works like a charm. You are brilliant sir, thank you.

 

[Fluff]

You're welcome & thanks for the feedback.