Filter If Contains (Specific Number)

[jamesjhhill]

Hi, I hope you guys can help.

I have a spreadsheet to help manage the production of a Children's TV series - see attached.

I basically want to filter all the 'types' of things dependant on whether they appear in an episode.

I've managed to do this to a point but the problem is that certain assets will appear in multiple episodes. So, Environment (with parent 17) might appear in Episodes 1, 5, 12, and 19.

I think I can manage to get it to filter everything that contains a '5' but the problem is that my filter list displays everything in episode '5' and '15' (because it contains a '5')

Any help would be greatly appreciated!

Thanks, James

[Glenn Kennedy]

Still using Excel 2016? If not, please update your profilr accordingly...

[alansidman]

I am a bit confused. Are you wanting to filter only on the items in Column L? And if this is the case what does "Any" mean? Does that mean you want to see all episodes in the filtered list? I do not understand this statement

I think I can manage to get it to filter everything that contains a '5' but the problem is that my filter list displays everything in episode '5' and '15' (because it contains a '5')

Would you clarify what you are attempting to do in that statement because I don't understand what that has to do with filtering the data based upon column L.

[Glenn Kennedy]

1. Change columnm A to double gidit text... 01 instead of 1, etc.

2. Replace DV in H2 to allow only double digit entries.

3. Use this:
=FILTER(A2:D21,(ISNUMBER(MATCH(B2:B21,IF(H1="Any",L3:L6,H1),0))*(ISNUMBER(SEARCH(H2,A2:A21)))),"")

1 and 2 are needed to prevent 15 being regarded as a 5....

[jamesjhhill]

Thanks Glenn, done!

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.