Create macro to filter on multiple criteria

[Guest]

I have 6 columns in my worksheet which are grouped into 3 pairs of columns.
Columns A, C and E are the current data for a given product and Columns B, D,
and F are the previous data. For instance, Column A result for cell 1 is
"January" and Column B result for cell 1 is "March". Similarly for the other
two sets of columns.

What I want to be able to do is filter to just those rows were the current
data is different from the previous data. Therefore, I need to filter on
Columns A, C, and E to just those rows where Column A is different than
Column B, or Column C is different than Column D, or Column E is different
than Column F.

I can do this individually for Columns A/B, or C/D, or E/F using advanced
filter but want to be able to 1) create a macro to do it, and 2) be able to
do it using all 3 sets of columns as the criteria.

Thanks!

 

[Guest]

One approach is to have separate columns that represent where the conditions
are met. For example, you could have column H be True if A=C, I be True if
B=D, J be True if C=E, K true is H=I=J=True. Then you can filter on any of
these conditional columns.

Not sure of your macro-writing ability, but you could also implement the
same functionality in code.

Regards,
Bill

 

[Guest]

One approach is to have separate columns that represent where the conditions
are met. For example, you could have column H be True if A=C, I be True if
B=D, J be True if C=E, K true is H=I=J=True. Then you can filter on any of
these conditional columns.

Not sure of your macro-writing ability, but you could also implement the
same functionality in code.

Regards,
Bill

 

[Guest]

Thanks, Bill. I'd like to be able to handle this without adding all of the
separate columns. Is there any way to do all of it via a basic macro without
having to create extra columns within the worksheet?

 

[Guest]

Yes, you can have the macro compare the values of the columns of interest and
then hide rows when they don't meet your filter criteria.

 

[Guest]

Unfortunately, I don't know how to write the macro so that it uses more than
one criteria range when I want it to use 3 different criteria ranges. Any
ideas?

 

[Guest]

You won't be using the native Excel filtering capability; you will need to
write it from scratch. Here's an example:

http://wcpii.com/Documents/MacroFilter.xls

 

[Guest]

Thanks, Bill. Sorry I didn't reply earlier but I was out of the office last
week. I'm pretty much a novice at macros so it will take me a while to
figure out exactly what you've done but it looks great!