Hi and thanks in advance,
I'm using Excel 2010 and trying to build a userform that uses AND and OR on a number of data columns and then using this criteria range on an Advanced filter. I have one sheet with the data and the criteria range off to the top right of the data and then on another sheet, I output the filtered data. This works well for a fairly basic set of ANDing or ORing criteria, where there are just two rows of critieria (so just the one OR). I understand that you AND everything on a single criteria row and that ORs are applied between the rows but wondering how that is represented on the sheet in the case of ORing a larger number of values. How would you output the criteria to the sheet for the Advanced filter?
Suppose I had criteria that I wanted to OR for each of 3 or more columns and then AND each set of ORed criteria ...
So if you were writing a formula, the logic would be something like:
(Below I've just included 4 criteria for each column as an example, but there could be more or less criteria for each column and more or less columns overall)
AND(OR(Col1="Dog",Col1="Cat", Col1="Rat", Col1="Bat", ...),OR(Col2="A", Col2="B",Col2="C",Col2="D",...),OR(Col3="1",Col3="2",Col3="3",Col3="4",...))