Macro filter with listbox

[rtlane]

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. 

[patrick_digan · Accepted solution]

@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

[rtlane]

I figured it out. Just did as shown below and it will automatically update the unique tool from the list box. Selected "Updated Field List" as the action type.