View Full Version : [SOLVED:] Filter formula include argument
garyj
11-27-2023, 11:33 AM
Greetings..
I am struggling with applying the Filter formula in an Excel table. I had it working in another instance, but it doesn't seem to be consistent.
Here is my formula...
=FILTER(BkgTbl[[#All],[Group]:[Rtn Depart Time]],({1,0,1,1,1,0,0,0,0,0,0,0,1,1,1,1,1,0,0,0,0,0,1,0,0,0,0,1})*(BkgRegT="Yes"),"")
BkgRegT is a named column range within BkgTbl. Removing the 2nd condition returns all the columns required properly. Adding the second condition returns a "#VALUE" error, and hovering over that says there is a data type problem. The table data in that column is either blank or "Yes".
What have I done wrong?
Thanks
Gary
p45cal
11-27-2023, 04:29 PM
I'm not at all clear on how many tables there are; is the 'applying the Filter formula in an Excel table' the same table as BkgTbl?
Putting the Filter function within any table will probably have Excel complaining.
Are you trying this:
=FILTER(FILTER(BkgTbl[[#All],[Group]:[Rtn Depart Time]],({1,0,1,1,1,0,0,0,0,0,0,0,1,1,1,1,1,0,0,0,0,0,1,0,0,0,0,1})),BkgTbl[[#All],[BkgRegT]]="Yes","")
if so, you'll get the same result with the shorter:
=FILTER(FILTER(BkgTbl[[Group]:[Rtn Depart Time]],({1,0,1,1,1,0,0,0,0,0,0,0,1,1,1,1,1,0,0,0,0,0,1,0,0,0,0,1})),BkgTbl[BkgRegT]="Yes","")
garyj
11-27-2023, 08:53 PM
There is one table: "BkgTbl".
[Group] is the header of col B and [Rtn Depart Time] is col AC.
BkgRegT was the name of a named range within the same table.
Yes, I had tried to separate the filters into two runs, but had the same errors. I don't know why, but I fiddled a bit with it again and found this to work...
=FILTER(FILTER(BkgTbl[[Group]:[Rtn Depart Time]],({1,0,1,1,1,0,0,0,0,0,0,0,1,1,1,1,1,0,0,0,0,0,1,0,0,0,0,1})),BkgRegT="Yes","")
Not sure on why... but it works. Yours didn't work, probably because the BkgRegT is a named column in the table and not a column header value. Yet, the second one is almost identical to the one that worked... just took out the Table name and turned the field into a named column.
Thanks for your help.
Gary
p45cal
11-28-2023, 03:14 AM
Not sure on why...BkgRegT and BkgTbl[[Group]:[Rtn Depart Time]] have the same number of rows.
BkgRegT and BkgTbl[[#All],[Group]:[Rtn Depart Time]] do not have the same number of rows.
garyj
11-28-2023, 10:10 AM
BkgRegT and BkgTbl[[Group]:[Rtn Depart Time]] have the same number of rows.
BkgRegT and BkgTbl[[#All],[Group]:[Rtn Depart Time]] do not have the same number of rows.
Thanks... sometimes my mindblock is larger than my mindsolve. :yes
Gary
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.