This would be used in creating the drop down. Getting a unique list of items (countries in this case). Step 2 – Creating The Dynamic Excel Filter Search BoxCreating a Dynamic Excel Filter Search BoxThis Dynamic Excel filter can be created in 3 steps: Step 1 – Getting a unique list of items If you have other MS Excel versions or a MAC you might not be able to complete all.Step 1 – Getting a unique list of items You can do this by selecting any cell in the dataset and using the keyboard shortcut Control + T. Here I would use three helper columns with formulas to extract the matching data.USEFUL TIP: It is almost always a good idea to convert your data into an Excel Table. Here I have used a Combo Box (ActiveX Control).One additional step is to create a named range for this unique list. This will remove duplicates and give you a unique list as shown below: In the Remove Duplicates dialogue box, select the column in which you have the list and click Ok. Select the country list –> Go to Data –> Remove Duplicates.See Also: How to use a formula to get a list of Unique items. Alternately, you can also you a formula to make this process dynamic. You can refer to wherever your unique list resides)NOTE: If you use ‘Remove Duplicates’ method and you expand your data to add more records and new countries, you will have to repeat this step again.
![]() It will insert the Combo Box. Click anywhere on the worksheet. If you do not have the Developer Tab visible, here are the steps to enable it. Go to Developer Tab –> Controls –> Insert –> ActiveX Controls –> Combo Box (ActiveX Controls). This way, whenever you enter anything in the Combo Box, it would also be reflected in a cell in real-time (as shown below). ![]() Creating A Database In Excel Serial Number ForI use three helper columns here to filter the data.Helper Column 1: Enter the serial number for all the records (20 in this case). Now, whatever you type would be reflected in cell K2 in real time)Finally, we link everything by helper columns. With the Combo Box selected, Go to Developer Tab –> Controls –> Click on Design Mode (this gets you out of design mode, and now you can type anything in the Combo Box. MatchEntry: 2-fmMatchEntryNone (this ensures that a word is not automatically completed as you type) This would show all the countries in the drop down). Set main search engine to google for chrome on macHere is the formula: =IFERROR(SMALL($F$4:$F$23,E4),"")This formula stacks all the matching row numbers together. To do this, we can use a combination if IFERROR and SMALL formulas. For example, if the Combo Box has the value ‘US’, all the records with country as ‘US’ would have the row number, and rest all would be blank (“”)Helper Column 3: In helper column 3, we need to get all the row numbers from Helper Column 2 stacked together. You can hide the original data and helper columns to show only the filtered records. =IFERROR(INDEX($B$4:$D$23,$G4,COLUMNS($I$3:I3)),"")INDEX – This extracts the data based on the row number.IFERROR – This returns blank when there is no data.Here is a snapshot of what you finally get:The Combo Box is a drop down as well as a search box. Copy it in the top-left cell where you want the data extracted, and then drag it down and to the right). This can be done easily using the index formula (insert this formula in where you want to extract the data. I would love to get your feedback and learn from you. For example, when you enter ‘I’, you may want to extract countries starting with I (as compared with the current construct where it would also give you Singapore and Philippines as it contains the alphabet I).As always, most of my articles are inspired by the questions/responses of my readers. This can be done exactly following the same steps with some modification in the formula in helper columns.Another variation could be to filter data that starts with the characters that you enter in the combo Box. For example, you may want to filter records where Sales Rep is Mike and Country is Japan. You may want to create multiple excel filters instead of one. How to Filter Cells with Bold Font Formatting in Excel. Creating a Drop Down Filter to Extract Data Based on Selection. How to Extract a Substring in Excel (Using TEXT Formulas). Create a Search Suggestion Drop Down List in Excel. Highlight Matching Data Using Conditional Formatting – Dynamic Search. It’s easier than the method shown in this tutorial.You May Also Like the Following Excel Tutorials:
0 Comments
Leave a Reply. |
AuthorJeff ArchivesCategories |