Filtering - adding multiple values to a filter list

[LRogers]

I feel like this should be so easy and straight forward but I'm struggling to figure out how to do this...

There are many times I want to filter for values "In a list" where it's a long list of items...perhaps a list of values from Excel, or values from a Word doc I've saved.  I would love to just copy all the values and paste them all in at once.  But when I go to "Add" and paste the values, it only pastes in the first value in the list.  It's time consuming to copy and paste each value individually and clicking through a "Get Values" list is not doable when there are hundreds/thousands of values to choose from.

I tried poking around the "Advanced Filter" option but was still not able to figure it out.  Any guidance would be appreciated.  

Here's an example of a use case:

I have a table with hundreds of Product Group IDs - I'd like to filter this table to only certain Product Group IDs that I have saved and categorized in an Excel file.  When creating the Query Builder, I'd like to simply filter on Product Group ID, use the "In a list" option, be able to copy all the IDs from my Excel list and paste them so they are all in my filter list.

I know there are options to import my external file but the size of the data I'm working with is so large that when I join to other tables it takes longer to query.  I would like to avoid any joins and simply just filter the single table itself.

Thank you!

[Patrick · Accepted solution]

That won't be possible. Using point & click only you will need to import your Excel and then define an inner join. 

You could alternatively also create a format with an other and then use this format in a where clause and only select the cases that don't match the other condition.

If writing code you could also use a data step, load the Excel data into a hash lookup table and then use the hash check() method to select the rows.

[LRogers]

Thank you for the options!  Was hoping there was a  trick to "copy and paste" but doesn't sound like it.

I'm not familiar with a Format or a hash lookup table...I will google to learn more.

Thanks for your time and help!

[Reeza]

A quick and dirty trick I sometimes use in Excel is to create a second column that formats the codes as needed, so it adds quotes and commas and then I can copy that list and paste it into my code more easily. 

e.g. (untested)  

B2

=concatenate("'", A2, "") (quotes are double quote, single quote, double quote)

If I'm feel more lazy:

C2 = B2

C3 = concatenate(b2, ", ", b3)

And drag that down. 

Then I use the values from the last column to copy/paste into my code.