Create macro to filter on multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
Bill Pfister said:
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
Click to expand...
 
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?
 
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.
 
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?
 
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!
 
Back
Top