# Filter Function...Is it capable?



## Jimmy110 (Dec 31, 2022)

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 (Dec 31, 2022)

***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 (Dec 31, 2022)

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 (Dec 31, 2022)

jdellasala said:


> 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))*
> ...


Thanks Jerry,

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


----------



## jdellasala (Dec 31, 2022)

I'm not showing the entire array here, just the good bits!
Book1AB1DateToday212/29/22 12:15 AM12/31/22 12:00 AM312/29/22 12:30 AM12/31/22 12:15 AM412/29/22 12:45 AM12/31/22 12:30 AM512/29/22 1:00 AM12/31/22 12:45 AMSheet7Cell FormulasRangeFormulaA2:A301A2=SEQUENCE(300,,DATEVALUE("12/29/2022")+TIMEVALUE("0:15:0"),TIMEVALUE("0:15:0"))B2:B97B2=FILTER(A2#,DATE(YEAR(A2#),MONTH(A2#),DAY(A2#))=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))Dynamic array formulas.


----------



## Jimmy110 (Dec 31, 2022)

jdellasala said:


> I'm not showing the entire array here, just the good bits!
> Book1AB1DateToday212/29/22 12:15 AM12/31/22 12:00 AM312/29/22 12:30 AM12/31/22 12:15 AM412/29/22 12:45 AM12/31/22 12:30 AM512/29/22 1:00 AM12/31/22 12:45 AMSheet7Cell FormulasRangeFormulaA2:A301A2=SEQUENCE(300,,DATEVALUE("12/29/2022")+TIMEVALUE("0:15:0"),TIMEVALUE("0:15:0"))B2:B97B2=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 (Dec 31, 2022)

Jimmy110 said:


> Thanks again...
> 
> I'm just getting a calc error?
> 
> ...


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! 
Book1BCDE1DateTimeFiltered DayFiltered Time212/29/202212:15:00 AM12/31/202212:00:00 AM312/29/202212:30:00 AM12/31/202212:15:00 AM412/29/202212:45:00 AM12/31/202212:30:00 AM512/29/202201:00:00 AM12/31/202212:45:00 AMSheet1Cell FormulasRangeFormulaB2:B301B2=DATE(YEAR(A2#),MONTH(A2#),DAY(A2#))C2:C301C2=TIME(HOUR(A2#),MINUTE(A2#),0)D2:E97D2=FILTER(B2:C301,B2#=TODAY())Dynamic array formulas.
I used the original array for convenience.


----------



## Jimmy110 (Dec 31, 2022)

jdellasala said:


> 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())))
> ...


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 (Dec 31, 2022)

Jimmy110 said:


> 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 (Dec 31, 2022)

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


----------



## Jimmy110 (Dec 31, 2022)

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


----------



## Peter_SSs (Jan 1, 2023)

Jimmy110 said:


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


In what way is it not working? What happens when you try?


----------



## Jimmy110 (Jan 1, 2023)

Peter_SSs said:


> In what way is it not working? What happens when you try?


I just got a #Calc! Error… but it was because the area I wanted to filter was part of the original Array👍🏻


----------



## Peter_SSs (Jan 1, 2023)

Jimmy110 said:


> I just got a #Calc! Error…


I was asking about in what way XL2BB was not working. I'm not sure that your answer to me was about that issue?


----------



## Jimmy110 (Jan 2, 2023)

Peter_SSs said:


> I was asking about in what way XL2BB was not working. I'm not sure that your answer to me was about that issue?


Sorry…. I think I’ve got mixed up with my responses!

Thanks very much for trying to help out! Got there in the end👌🏻


----------



## Peter_SSs (Jan 2, 2023)

Jimmy110 said:


> Got there in the end👌🏻


Yes, but have you got XL2BB working so that you can use it if you have another question?


----------



## Jimmy110 (Jan 2, 2023)

Peter_SSs said:


> Yes, but have you got XL2BB working so that you can use it if you have another question?


No, it’s still just greyed out on the task bar


----------



## Peter_SSs (Jan 2, 2023)

Jimmy110 said:


> No, it’s still just greyed out on the task bar


See if this helps: Xl2bb got disable


----------



## Jimmy110 (Jan 3, 2023)

Peter_SSs said:


> See if this helps: Xl2bb got disable


Sorted!  Thanks very much....was just not in a trusted location.

Cheers

James


----------



## Peter_SSs (Jan 3, 2023)

Jimmy110 said:


> Thanks very much....was just not in a trusted location.


Great. Glad you got it going.


----------

