Filter Function...Is it capable?

[Jimmy110]

Hi

I'm using =FILTER function to create a spilled column with numbers in Column A and Date and Time stamps in column B.... Is there a way to create another Filter function off the already filtered column? (Without selecting filters in the standard way as I cant have any of the data disappear in the lines that get hidden when filtering this way?)

The reason I ask, is I want to filter column A & B again if its todays date in column B?

Cheers

James

 

[Jimmy110]

***UPDATE***

I think what I am asking is...Can you do a nested Filter Function that filters on 2 elements, the first being an absolute value, but the second being a date range that includes any date/time that are today?

Cheers

 

[jdellasala]

The FILTER function does not hide anything. It RETURNS a spilled array of VALUES based on the filter.
It might help to post an example of what you're trying to achieve. Use XL2BB though!
I'm not aware of any problem with nesting FILTERS though, but it would be better to just put it in one filter:
=FILTER(Array, (StuffA=1)*(StuffB=2))
or
=FILTER(Array, (StuffA=1)+(StuffB=2))
If you put the two filters within parenthesis and use multiplication for an AND filter, and addition for an OR filter.

 

[Jimmy110]

The FILTER function does not hide anything. It RETURNS a spilled array of VALUES based on the filter.
It might help to post an example of what you're trying to achieve. Use XL2BB though!
I'm not aware of any problem with nesting FILTERS though, but it would be better to just put it in one filter:
=FILTER(Array, (StuffA=1)*(StuffB=2))
or
=FILTER(Array, (StuffA=1)+(StuffB=2))
If you put the two filters within parenthesis and use multiplication for an AND filter, and addition for an OR filter.

Thanks Jerry,

How do I write "any date and time that is today" in the StuffB=2 bit?

 

[jdellasala]

I'm not showing the entire array here, just the good bits!

Book1
	A	B
1	Date	Today
2	12/29/22 12:15 AM	12/31/22 12:00 AM
3	12/29/22 12:30 AM	12/31/22 12:15 AM
4	12/29/22 12:45 AM	12/31/22 12:30 AM
5	12/29/22 1:00 AM	12/31/22 12:45 AM
Sheet7

Cell Formulas
Range	Formula
A2:A301	A2	=SEQUENCE(300,,DATEVALUE("12/29/2022")+TIMEVALUE("0:15:0"),TIMEVALUE("0:15:0"))
B2:B97	B2	=FILTER(A2#,DATE(YEAR(A2#),MONTH(A2#),DAY(A2#))=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))
Dynamic array formulas.

 

[Jimmy110]

I'm not showing the entire array here, just the good bits!

Book1
	A	B
1	Date	Today
2	12/29/22 12:15 AM	12/31/22 12:00 AM
3	12/29/22 12:30 AM	12/31/22 12:15 AM
4	12/29/22 12:45 AM	12/31/22 12:30 AM
5	12/29/22 1:00 AM	12/31/22 12:45 AM
Sheet7

Cell Formulas
Range	Formula
A2:A301	A2	=SEQUENCE(300,,DATEVALUE("12/29/2022")+TIMEVALUE("0:15:0"),TIMEVALUE("0:15:0"))
B2:B97	B2	=FILTER(A2#,DATE(YEAR(A2#),MONTH(A2#),DAY(A2#))=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))
Dynamic array formulas.

Thanks again...

I'm just getting a calc error?

I've also tried to use the XL2BB add in but its not working?

The formula I'm using at the moment is:

=FILTER(C:D,F:F=N2)

where c is the column containing the numbers and D is the column containing the date and time stamp, column F being the name. N2 being the cell i type the name in and it dynamically filters everything really well....BUT, I just want the numbers that were entered today.

Whenever I add something that logically should work (Including trying your suggested formula) after the current formula above, I just get a calc error?

Slowly going insane LOL

 

[jdellasala]

Thanks again...

I'm just getting a calc error?

I've also tried to use the XL2BB add in but its not working?

The formula I'm using at the moment is:

=FILTER(C:D,F:F=N2)

where c is the column containing the numbers and D is the column containing the date and time stamp, column F being the name. N2 being the cell i type the name in and it dynamically filters everything really well....BUT, I just want the numbers that were entered today.

Whenever I add something that logically should work (Including trying your suggested formula) after the current formula above, I just get a calc error?

Slowly going insane LOL

Why aren't you using the formula? What's the data?

=FILTER(A2#,DATE(YEAR(A2#),MONTH(A2#),DAY(A2#))=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))

What's C:D? Are the Date and Time in DIFFERENT COLUMNS? That's a HUGE difference!

Book1
	B	C	D	E
1	Date	Time	Filtered Day	Filtered Time
2	12/29/2022	12:15:00 AM	12/31/2022	12:00:00 AM
3	12/29/2022	12:30:00 AM	12/31/2022	12:15:00 AM
4	12/29/2022	12:45:00 AM	12/31/2022	12:30:00 AM
5	12/29/2022	01:00:00 AM	12/31/2022	12:45:00 AM
Sheet1

Cell Formulas
Range	Formula
B2:B301	B2	=DATE(YEAR(A2#),MONTH(A2#),DAY(A2#))
C2:C301	C2	=TIME(HOUR(A2#),MINUTE(A2#),0)
D2:E97	D2	=FILTER(B2:C301,B2#=TODAY())
Dynamic array formulas.

I used the original array for convenience.

 

[Jimmy110]

Why aren't you using the formula? What's the data?

=FILTER(A2#,DATE(YEAR(A2#),MONTH(A2#),DAY(A2#))=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))

What's C:D? Are the Date and Time in DIFFERENT COLUMNS? That's a HUGE difference!

Book1
	B	C	D	E
1	Date	Time	Filtered Day	Filtered Time
2	12/29/2022	12:15:00 AM	12/31/2022	12:00:00 AM
3	12/29/2022	12:30:00 AM	12/31/2022	12:15:00 AM
4	12/29/2022	12:45:00 AM	12/31/2022	12:30:00 AM
5	12/29/2022	01:00:00 AM	12/31/2022	12:45:00 AM
Sheet1

Cell Formulas
Range	Formula
B2:B301	B2	=DATE(YEAR(A2#),MONTH(A2#),DAY(A2#))
C2:C301	C2	=TIME(HOUR(A2#),MINUTE(A2#),0)
D2:E97	D2	=FILTER(B2:C301,B2#=TODAY())
Dynamic array formulas.

I used the original array for convenience.

C is the weight of the thing thats being measured, D is the DD/MM/YY HH:MM:SS (All in one column)

I wish I could share the sheet with XL2BB but its all greyed out on the toolbar

...maybe its because the column I'm trying to filter is the original array? There's probably an easy way that I'm missing something really obvious!

 

[jdellasala]

C is the weight of the thing thats being measured, D is the DD/MM/YY HH:MM:SS (All in one column)

I wish I could share the sheet with XL2BB but its all greyed out on the toolbar

...maybe its because the column I'm trying to filter is the original array? There's probably an easy way that I'm missing something really obvious!

Then my initial post should work fine.

 

[Jimmy110]

I think Ive figured it out.... It was creating a circular ref because the column I was referencing was part of the original array.

So I've inserted a ref column with the =TRUNC function to reference the date and time column to make it just the date, then added that into the =FILTER function and its working!

Thanks for your help....got there in the end