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

charliebigpot

New Member
Joined
Aug 20, 2019
Messages
1
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".


ABCDEFG
1Filter (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)
2Tomato
3Stones
4Melon
5Avocado
6Trees
7
8StepConcatenated
9Step1
TomatoStonesMelonAvocadoTrees

<tbody>
</tbody>
TomatoStonesMelonAvocadoTrees
10Step2TomatoMelonAvocadoTreesTomatoMelonAvocadoTrees
11Step3StonesStones
12Step4TomatoStonesMelonAvocadoTreesTomatoStonesMelonAvocadoTrees
13Step5AvocadoTreesAvocadoTrees

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

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,579
Messages
5,523,705
Members
409,531
Latest member
Lmfacc

This Week's Hot Topics

Top