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".


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]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)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Tomato[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Stones[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Melon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Avocado[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Trees[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Step[/TD]
[TD]Concatenated[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Step1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]TomatoStonesMelonAvocadoTrees[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Tomato[/TD]
[TD]Stones[/TD]
[TD]Melon[/TD]
[TD]Avocado[/TD]
[TD]Trees[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Step2[/TD]
[TD]TomatoMelonAvocadoTrees[/TD]
[TD]Tomato[/TD]
[TD][/TD]
[TD]Melon[/TD]
[TD]Avocado[/TD]
[TD]Trees[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Step3[/TD]
[TD]Stones[/TD]
[TD][/TD]
[TD]Stones[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Step4[/TD]
[TD]TomatoStonesMelonAvocadoTrees[/TD]
[TD]Tomato[/TD]
[TD]Stones[/TD]
[TD]Melon[/TD]
[TD]Avocado[/TD]
[TD]Trees[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Step5[/TD]
[TD]AvocadoTrees[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Avocado[/TD]
[TD]Trees[/TD]
[/TR]
</tbody>[/TABLE]

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.