Nov 15 2020 10:03 PM
Hello Everyone:smiling_face_with_smiling_eyes:
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
Nov 16 2020 12:36 AM
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)
Nov 16 2020 12:47 AM
@Excel ,,
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.
Nov 16 2020 05:24 AM
Or Power Query
Nov 17 2020 01:01 AM
Nov 17 2020 11:01 AM
Nov 18 2020 12:46 AM - edited Nov 18 2020 01:17 AM
@Excel ,,
I would like to suggest few Non programming methods as well few Programming.
Non Programming method 1:
How it works:
How to draw check boxes.
Non Programming method 2:
=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:
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.
Programming method 2:
Before you filter:
How it works:
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.
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.