Macro filter with listbox

Hi,

I'm trying to make a macro that has a list box with 4 options for filtering a single column from an excel spreadsheet input. I can set it up to filter one at a time by using the contains function: 1 or 2 or 3 or 4. The issue i'm having is figuring out a formula for a combination of the 4 options. For example if I want to filter for 1 and 2.

Macros

Accepted answers

patrick_digan

@rtlane There are a number of ways to accomplish this. The way I normally do it is to bake my formula into the list box tool. In the start text box, I would write

Contains([Technology],"

The Separator box would be

") OR Contains([Technology],"

and the end text box would be

")

This is a simple way because you wouldn't have to add any tools or write a strange formula into your action tool.

EDIT: You would then need to change your action tool to replace the entire contents of your filter instead of just the 1 string. You would just uncheck the box.

All comments

patrick_digan

@rtlane There are a number of ways to accomplish this. The way I normally do it is to bake my formula into the list box tool. In the start text box, I would write

Contains([Technology],"

The Separator box would be

") OR Contains([Technology],"

and the end text box would be

")

This is a simple way because you wouldn't have to add any tools or write a strange formula into your action tool.

EDIT: You would then need to change your action tool to replace the entire contents of your filter instead of just the 1 string. You would just uncheck the box.

scottj

You can use an IN statement and then replace a specific string.

Here is the Filter formula

[a] in ("1","2","3")

and then in the Action tool replace the field names and quotes you are creating.

rtlane

Thank you, that worked like a charm

rtlane

thanks Scottj. For some reason I was unable to get that to work before. My filter options are not exact and that's why I needed the contain function. I tried to combine the IN function and the CONTAINS function, but it did want to accept it.

patrick_digan

@rtlane For future reference, you could also use the macro @MarqueeCrew created which essentially combines the contains and in functionality. It would look like this where his macro would replace your filter. This would allow you to pass a comma separated list to his macro:

rtlane

Thank you patrick.

I added a feature where the the user can select the fields from the select tool. Is there a way I can make it where there is a unique tool after the select tool and the unique tool is updated with the same fields that was selected in the filter automatically?

For example:

User selects the fields: A, B, C - in the select store

Unique tool is updated with the same exact selections

rtlane

Depending on which selections are made in the select tool, I want the unique tool selections to match the select tool. Using the example below, if I remove the column "phone numbers" using the select tool and just leave "Name" selected, then I would want the unique tool to also just have "Name" as the selection. Leaving me with a column with 2 records, Jane and Jill.

Name	Phone Numbers
Jane	555-555-5555
Jane	111-111-1111
Jane	222-222-2222
Jill	777-777-7777
Jill	888-888-8888

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335