This can be done by using a really simple VBA code mentioned below: Private Sub TextBox1_Change()ĪctiveSheet.ListObjects("Data").Range.AutoFilter Field:=2, Criteria1:= "*" & & "*", Operator:=xlFilterValues If you’re not using Office 365 and don’t have access to the FILTER function, you can still create the ‘filter as you type’ search box in Excel. Now when you enter A in the search box, it will only give you records for Asia. In case you want to have the text string (that you enter in the search box) at the beginning only, you can use the below formula instead: =FILTER(A4:C13,LEFT(B4:B13,LEN(E1))=E1)
The position of the text string in the cells in column B is not checked. For example, if you enter ‘a’ in the text box, it would return all the records for Canada, Asia, and Brazil. Note that this formula checks whether the text string entered in the text box appears in the cells in column B or not. The ISNUMBER function is used to get TRUE if there is a match and the cell returns a number, and FALSE is it returns the error.īased on this condition, the data is filtered as you type. All the cells that have the text will return a number and those that don’t will return the #VALUE! error. The SEARCH formula checks whether the value entered in the text box (which also automatically gets entered in cell E1) is there in the cells in column B or not. The above formula uses the FILTER function with the array as the original dataset and the condition uses the SEARCH formula. Now that we have linked the text box to a cell, the last step is to filter the data based on the value in the text box (which in turn would be the value in cell E1)įor this, we need to enter the FILTER formula in cell E4, so that results are filtered and shown there.īelow is the formula that will now filter the results as soon you can enter anything in the text box: =FILTER(A4:C13,ISNUMBER(SEARCH(E1,B4:B13))) Now, when you enter any text in the text box, you will notice that it appears in cell E1 in real-time (as you’re typing) You will notice that it turns from dark gray to light gray (indicating that it’s not enabled now).
In the Excel Options dialog box that opens, check the Developer option in the right pane and click OK. You can either change it by overwriting the above-mentioned event or choose the event from the second drop down list in the same window.In case you don’t see the Developer tab in the ribbon (in Step 1), right-click on any of the tabs and click on ‘Customize the Ribbon’. We will write our duplicate Locator code inside another event called Worksheet_Change. In the drop down list, choose Worksheet, which will add a default procedure called Worksheet_SelectionChange. Just above the window, you will see two drop down list, choose the first (showing General). In the project explorer, find Sheet1 and double click it. To do this click the View menu at top left corner of the VBA project or click Ctrl + r. This shortcut key combination will open a VBA project, where you can write the code. Open a new Excel file and click Alt + F11. Related: Find Duplicate in Excel using “Conditional Formatting” Once you delete the duplicate text, it will remove the highlight (color red) and set the default color (color black). The highlighting of the text will help you locate the value, instantly. It will not just find, but also highlight the duplicate text. The macro or the codes primary job is to find duplicate values while typing in the values in a particular column. VBA stands for Visual Basic for Applications, a language that allows us to communicate with Excel.
#Excel find duplicates while typing how to#
Therefore, I decided to share a little piece of code with you, on how to find duplicates in an Excel sheet using VBA.
0 kommentar(er)
