Forum Discussion
FILTER Function - "include" parameter as string from another cell
Hi All
I have a table on Worksheet A, unimaginatively named "Table1", with fields including "CODE".
On Worksheet B, in cell C1, I have this formula:
=FILTER(Table1,(Table1[CODE]="T1") + (Table1[CODE]="P1"))
This works fine i.e. it shows a new table with all the rows where CODE = "T1" or "P1". Lovely.
Also on Worksheet B, in cell A1, I have the following string:
(Table1[CODE]="T1") + (Table1[CODE]="P1")
I have created this string using logic based on the data in Table1. On another day, the string literals might change and there may be more OR-ed elements, perhaps:
(Table1[CODE]="ENG002") + (Table1[CODE]="BBBB") + (Table1[CODE]="Z YW")
Essentially this string is volatile and I don't want to hard-code it as in the first example.
How can I use successfully the string in cell A1 as the 'include' parameter to the FILTER function?
I tried:
=FILTER(Table1,A1)
but this gives #VALUE!
I thought INDIRECT might work but:
=FILTER(Table1,INDIRECT(A1))
gives a #REF!
I think I am missing something obvious but can't see it. Can you help, at all? Thanks VM.
Peter
The first thing that comes to mind is using the filter dropdown in Table1; the second is using slicers instead.
Detlef_Lewin thanks for the reply.
I know how to use the drop-downs and I have even previously written VBA to do the filtering (in a different scenario).To be honest I've no idea what a slicer is - that's for another day I think.
I just thought that I'd try the FILTER function, which presents just the extracted records - which is a neat idea (particularly if I can get it to only bring through certain columns). The problem is how to get a string, held in a cell, to be the 'include' parameter. I wouldn't have thought it could be hard but I've been scratching my head for some time on this. 🙂
Thanks for your time, all the same - much appreciated.
=FILTER(Table11,BYROW(Table11[Code]=TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1),"Table1[CODE]=",""),"(",""),")",""),"""",""),{" + ",";"}),LAMBDA(r,OR(r))))
With this FILTER you can use e.g.
(Table1[CODE]="BBBB") + (Table1[CODE]="T1") + (Table1[CODE]="ENG002") + (Table1[CODE]="P1")
in cell A1.
paejunk Here is another alternative:
For the set of options enter them in a cell like this:
={ option1, option2, option3, ....}then use a formula like this:
=FILTER($G$1#,MMULT(--($G$1#=$I$1#),SEQUENCE(COLUMNS($I$1#),,1,0)))where G1# defines the conditional range and I1 is where the options are (in format noted above)
EDIT: I noticed your recent comment you are trying to understand all the options and I noticed I didn't do a good job equating this formula to your situation. I notice you use structured references for a table so based on the formula:
=FILTER(Table1,(Table1[CODE]="T1") + (Table1[CODE]="P1"))
let me update the above so in cell A1 would be the formula:
={"T1", "P1"}
this is nothing more than an array of constants which will 'spill' across columns. Alternatively you could use semicolons to 'spill' down rows but the formula would need a TRANSPOSE or you can use simple Text and use the formula TEXTSPLIT to create the array. In anycase, the point is to be flexible for more or less options being entered.
The formula would then be put in row 2 to leave row 1 for the spill from A1:
=FILTER(Table1,MMULT(--(Table1[CODE]=$A$1#),SEQUENCE(COLUMNS($A$1#),,1,0)))
alternatively if you use Text in A1 I would use a simple comma deliminated list like:
T1, P1
then the whole list is in that 1 cell A1 but the following formula requires text manipulation like so:
=FILTER(Table1,MMULT(--(Table1[CODE]=TRIM(TEXTSPLIT($A$1,",")) ),SEQUENCE(COLUMNS(TEXTSPLIT($A$1,",")),,1,0)))
And finally since you genuinely seem interested in understanding these options here is a quick explanation:
FILTER() will filter an input or rows (or columns) based on a criteria. That criteria must be a column (or row) with the same number of elements as the input and will be checked as TRUE or FALSE for each. note any value other than zero is considered TRUE. So in the above I compare the column of CODE with a row of acceptable options to create a table of 0s and 1s and then use MMULT to convert that table into a single column were each row is essentially OR'd together. I use MMULT instead of OR because OR would OR the ENTIRE table of 0s and 1s and not do each ROW individually.
I hope that helps.
Hi Lorenzo
I was bowled over with the massive support and help I received. I'm still going through it - clearly there are new functions that I need to get to grasp with - and all very interesting. Once I've chosen a best route, I'll be sure to let you know. Thanks very much.
Peter
