Forum Discussion
Combining FILTER, MID, and FIND functions
We use Excel to manage logs from many agricultural trials. There is a central 'live' table where live data for all trials gets sent to, but ultimately only the relevant data needs to be sent to each individual trial excel file in a table. Each Trial has a unique code, and I'm trying to use the filter function to populate a table on each individual trial file with data from the live central data table, using the unique code as an identifier.
Here is a portion of the table:
And here is what's currently working. I'm using the RIGHT function to include just the right 11 characters in the "Trial" column in the FILTER 'include' function, which is the trial code, to pull the relevant data across:
=FILTER(OnlineSprayBookAllData[[Date]:[Photo or Video]],(RIGHT(OnlineSprayBookAllData[Trial],11))=Table5[@SITE1],"Please check GOA code is entered in SITE_DATA")
BUT what I'd really like to do is combine the FILTER, MID, and FIND functions to be able to pull the relevant data across even if there are multiple trial codes within the trial column in the live data table. This would make the data entry process much more efficient and we could capture data for multiple trial sites at a time. Like this:
=FILTER(OnlineSprayBookAllData[[Date]:[Photo or Video]],(MID(OnlineSprayBookAllData[Trial],(FIND(Table5[@SITE1],OnlineSprayBookAllData[Trial],1)),11))=Table5[@SITE1],"Please check GOA code is entered in SITE_DATA")
As you can see, It does not work. It does work if you select just a single cell in the FIND 'within_text' portion of the function, but not if you select the whole table column. I've had the same lack of success using the INDEX SMALL functions. Is there anyway I can make this work??
Warmly,
Jos.
How about
=FILTER(OnlineSprayBookAllData[[Date]:[Photo or Video]], ISNUMBER(SEARCH(Table5[@SITE1], OnlineSprayBookAllData[Trial])), "Please check GOA code is entered in SITE_DATA")
Does it work if you use SEARCH instead of FIND? FIND does not return an array of values, but SEARCH does.
- josiahmayCopper Contributor
Thanks for the feedback, but unfortunately no, I haven't been able to make it work with SEARCH.
How about
=FILTER(OnlineSprayBookAllData[[Date]:[Photo or Video]], ISNUMBER(SEARCH(Table5[@SITE1], OnlineSprayBookAllData[Trial])), "Please check GOA code is entered in SITE_DATA")