Filter based on a list of items - multiple items filter

%3CLINGO-SUB%20id%3D%22lingo-sub-1889465%22%20slang%3D%22en-US%22%3EFilter%20based%20on%20a%20list%20of%20items%20-%20multiple%20items%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1889465%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%3Asmiling_face_with_smiling_eyes%3A%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20a%20master%20list%20of%20product%20that%20have%20about%201000%20items%20given%20by%20our%20vendor%20(like%20a%20catalogue%20but%20in%20excel%20file)%2C%20the%20column%20have%20price%2C%20UPC%2C%20product%20ID%20etc..%20Now%2C%20i%20have%20a%20list%20of%20product%20Item%20that%20I%20would%20like%20to%20filter%20from%20the%20master%20product%20list.%20I%20know%20i%20can%20filter%20product%20id%20%2C%20however%2C%20this%20would%20required%20me%20to%20manual%20select%20the%20product%20ID.%20I%20wonder%20is%20there%20a%20way%20i%20can%20select%20a%20range%20of%20product%20ID%20that%20i%20wanted%20to%20filter%20and%20apply%20it%20to%20the%20master%20list%20so%20that%20only%20the%20product%20in%20my%20list%20are%20filter%20out.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20help%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1889465%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1889694%22%20slang%3D%22de-DE%22%3ESubject%3A%20Filter%20based%20on%20a%20list%20of%20items%20-%20multiple%20items%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1889694%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend%20you%2C%20add%20a%20file%20(without%20sensitive%20data)%20to%20your%20project.%3C%2FP%3E%3CP%3EExplain%20your%20plans%20in%20relation%20to%20this%20file.%20So%20you%20can%20get%20a%20solution%20that%20is%20tailored%20to%20your%20needs%20much%20faster.%3C%2FP%3E%3CP%3EAt%20the%20same%20time%2C%20it%20is%20much%20easier%20for%20someone%20who%20wants%20to%20help%20understand%20the%20subject.%3C%2FP%3E%3CP%3EA%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3EPlease%20no%20Picture%2C%20even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Knowing%20the%20Excel%20version%20and%20operating%20system%20would%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1889711%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20based%20on%20a%20list%20of%20items%20-%20multiple%20items%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1889711%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20selected%2Fspecific%20Id's%20to%20filter%20then%20you%20need%20programming%20method%2C%20is%20%3CFONT%20size%3D%222%22%3EVBA%26nbsp%3B%3C%2FFONT%3Emacro%20or%20another%20is%20Advance%20filter%20or%20Formula%20%3CSPAN%3Eb%3C%2FSPAN%3Eased%20method.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1890491%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20based%20on%20a%20list%20of%20items%20-%20multiple%20items%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1890491%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20Power%20Query%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hello Everyone

I have a master list of product that have about 1000 items given by our vendor (like a catalogue but in excel file), the column have price, UPC, product ID etc.. Now, i have a list of product Item that I would like to filter from the master product list. I know i can filter product id , however, this would required me to manual select the product ID. I wonder is there a way i can select a range of product ID that i wanted to filter and apply it to the master list so that only the product in my list are filter out.

 

Please help

6 Replies

@Zan_Hanifee 

With your permission, if I can recommend you, add a file (without sensitive data) to your project.

Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

 

* Knowing the Excel version and operating system would also be an advantage.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

@Zan_Hanifee ,,

 

If you want selected/specific Id's to filter then you need programming method, is VBA macro or another is Advance filter or Formula based method.

Please confirm through reply whether you are comfortable with Macro then I'll show you few methods fixes the issue.
Yes sir, please tell me a another method..

@Zan_Hanifee ,,

 

I would like to suggest few Non programming methods as well few Programming.

 

Non Programming method 1:

Rajesh-S_0-1605687594976.png

 

How it works:

  1. Enter IDs to filter, in E145:E148 (is adjustable also).
  2. Draw Check boxes in A151:A161.

How to draw check boxes.

  1. From DEVELOPER TAB hit Design.
  2. Left to it is Insert.
  3. From ActiceX controls section, find Check box, and plot one by one in A151:A161.
  4.  Select every check box one by one, Right click & from Properties, find Linked Cell and enter related linked cell's reference from Column E. Like for Check box in A151 Linked cell is E151. 
  5. Turn off the Design mode.
  6. Now click the related Check box for the IDs you want to filter, Excel will fill related cells in Column E with TRUE.
  7. Now apply Auto Filter for TRUE.

Non Programming method 2:

  1. Enter this Formula in cell D151 & fill it down.

 

 

=COUNTIF($E$145:$E$148,B151)

 

 

 

     2. Excel will fill cells in column D with 1 and 0s.

     3. Now filter for 1s.

 

Programming method 1:

  1. Using DEVELOPER TAB then Design & Left to it is Insert then, from ActiceX controls draw the Command button.
  2. Double click the Command button, you get Veditor. 
  3. Copy & Paste this code.

 

 

Private Sub CommandButton1_Click()
 
 Dim Arr As Variant
  Dim i As Integer
    Arr = WorksheetFunction.Transpose(Worksheets("Sheet1").Range("E145:E148").Value)
      For i = LBound(Arr) To UBound(Arr)
        Arr(i) = CStr(Arr(i))
      Next i
    
    ActiveSheet.Range("$B$150:$B$161").AutoFilter Field:=1, Criteria1:=Arr, Operator:=xlFilterValues

End Sub

 

 

   5. Press Alt+Q to return to the Sheet. 

   6. Now save the workbook as Macro enabled (*.xlsm).

   7. Turn Off the Design mode.

   8. Now click the command button.

 

Excel will filter all IDs are stored in cell E145:E148.

Rajesh-S_0-1605689155824.png

 

Programming method 2:

 

Before you filter:

Rajesh-S_2-1605689555233.png

How it works:

  1. Either press Alt+F11 or select Sheet name, Right click & from menu hit View Code.
  2. You get VB editor, Copy & Paste this code as Standard module.

 

 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim xcolumn As Integer

Dim xvalue As String

xcolumn = ActiveCell.Column

xvalue = ActiveCell.Value

    If Application.Intersect(ActiveCell, [Headers]) Is Nothing Then

        If ActiveCell.Value <> "" Then

ActiveSheet.Range("A:B").AutoFilter Field:=xcolumn, Criteria1:=xvalue

        Cancel = True

        End If

    End If

End Sub

 

 

    3. Press Alt+Q to return to Sheet.

    4. Select A1:B1, and use Range Name method and assign name Headers to them.

    5. Save the Workbook as Macro enabled (*.xlsm).

    6. Now, Double Click the ID you want filter, you get this.

 

After you Double click the ID.

Rajesh-S_4-1605689937427.png

Remember that you can't select more than one ID at a time to filter.

 

**** Note: If you find all these are working for you, then you may mark this post as best Answer as well like.