Combining FILTER, MID, and RIGHT functions

[JosMay]

Hi there,

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. 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 FILTER and 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 as 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.

 

[hagia_sofia]

Could you please post those images again so that they are readable?

 

[JosMay]

Hi, my apologies! I didn't realise they weren't readable. If since found the answer in another forum though. For anyone that lands here, the key was using FILTER, ISNUMBER, and SEARCH together: =FILTER(OnlineSprayBookAllData[[Date]:[Photo or Video]], ISNUMBER(SEARCH(Table5[@SITE1], OnlineSprayBookAllData[Trial])), "Please check GOA code is entered in SITE_DATA")

 

[Joe4]

Hi, my apologies! I didn't realise they weren't readable. If since found the answer in another forum though.

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!
If you do cross-post, please be sure to ALWAYS provide the links to the other posts, then there shouldn’t be a problem.