Autofilter with 2+ cell references with "or" and "contains"

[charliebigpot]

Hi there, I'm a long time listener (reader) first time caller (writer).

I have scanned through dozens of forums and topics but can't seem to find a working solution for this challenge:
I have a table with multiple rows where I'm trying to autofilter a specific column based on 2+ inputs in specific cells based on "contains" and "or".

	A	B	C	D	E	F	G
1		Filter (the way I built it is that B2-B6 are auto populated based on a dropdown selection in a different sheet - if only one item is selected in the dropdown, only one will show up here, if all are selected in the dropdown, all show up here underneath eachother)
2		Tomato
3		Stones
4		Melon
5		Avocado
6		Trees
7
8	Step	Concatenated
9	Step1	TomatoStonesMelonAvocadoTrees <tbody> </tbody>	Tomato	Stones	Melon	Avocado	Trees
10	Step2	TomatoMelonAvocadoTrees	Tomato		Melon	Avocado	Trees
11	Step3	Stones		Stones
12	Step4	TomatoStonesMelonAvocadoTrees	Tomato	Stones	Melon	Avocado	Trees
13	Step5	AvocadoTrees				Avocado	Trees

<tbody>
</tbody>

If I pick only "stones" it should autofilter "concatenated" on step1/3/4.
If I pick "tomato", "stones" and "melon" it should autofilter "concatenated" on step1/2/3/4 since it's an "or" and "contains" condition.

Does this make sense? I tried to re-think the structure but can't come up with a better solution than concatenating since I want to use "or" and "contains".

Thanks to anyone who makes the time to read through all of this.

 

[pbornemeier]

If each of your columns has a unique header then you could use SQL in Excel VBA to copy the rows that met your requirements to another sheet.