Create macro to filter on multiple criteria
 

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!

Create macro to filter on multiple criteria
 

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


"csdjj021191" wrote:

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!

Create macro to filter on multiple criteria
 

"Bill Pfister" wrote:

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


"csdjj021191" wrote:

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!

Create macro to filter on multiple criteria
 

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?

"Bill Pfister" wrote:

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


"csdjj021191" wrote:

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!

Create macro to filter on multiple criteria
 

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.


"csdjj021191" wrote:

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?

"Bill Pfister" wrote:

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


"csdjj021191" wrote:

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!

Create macro to filter on multiple 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?

"Bill Pfister" wrote:

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.


"csdjj021191" wrote:

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?

"Bill Pfister" wrote:

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


"csdjj021191" wrote:

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!

Create macro to filter on multiple criteria
 

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


"csdjj021191" wrote:

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?

"Bill Pfister" wrote:

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.


"csdjj021191" wrote:

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?

"Bill Pfister" wrote:

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


"csdjj021191" wrote:

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!

Create macro to filter on multiple criteria
 

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!

"Bill Pfister" wrote:

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


"csdjj021191" wrote:

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?

"Bill Pfister" wrote:

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.


"csdjj021191" wrote:

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?

"Bill Pfister" wrote:

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


"csdjj021191" wrote:

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!