Select a cell and type this formula =INDEX(A2:A11, MATCH(0, COUNTIF(C1:$C$1, A2:A11)+IF(COUNTIF(A2:A11, A2:A11)>1, 0, 1), 0)) into it, then press Shift + Ctrl + Enter keys. By using a formula, we can compare two columns and display the duplicate values. Look at the following screenshot, I have column A (A2:A15) and column C (C2:C13) which contain some duplicate names. Here I introduce two formulas for you to extract duplicates. Find duplicate values in two columns with Formula.
Now I will tell you some quick methods to extract duplicates from one column in Excel.Įxtract duplicates from one column with formulasĮxtract duplicates from one column with Kutools for Excel If you have a column of product list, and some of them are duplicates, now you need to extract only duplicates from this list to other column, how can you solve? In general, you may find them one by one then type them manually. If your Excel table contains empty rows/cells, here's a simple tip to select them:įirst select the concerned cells and press the F5 key.How to extract all duplicates from a column in Excel? Click OK and the duplicates will be removed instantly. If you’d like to apply changes in this document, select Filter the list, in-place, otherwise, you can copy and save it to another location by selecting the second option. In the new dialog box choose Unique records only. Next, go to Data and in the Sort & Filter section select Advanced. Your data might contain duplicate records that repeat. To do so, select any cell or specific data to be filtered. If you're a Microsoft Excel user, you've likely been asked to work with messy data in a spreadsheet before. Select all data range including the formula cell, and click Data > Filter to enable Filter function. Select a blank cell next to the data range, D2 for instance, type formula A3A2, drag auto fill handle down to the cells you need. The second way to delete duplicates is to use a filter for unique values. With a formula and the Filter function, you can quickly remove duplicates but keep rest.
How to Delete Duplicate Data with Filters In the dialog box choose the columns from which you wish to delete duplicates and click on Remove Duplicates. To remove duplicates in Excel sheets follow these simple steps: Select the rows from which you’d like to remove duplicate data, go to Data tab and select Remove Duplicates. Now cells that have the duplicates will be highlighted with a colour you have chosen. In the new pop-up window select duplicate values in the selected range and choose the colour you’d like to use to highlight the duplicates. Select and highlight all the data you want to search for duplicates (don’t include headers), then select the Home tab and in the Styles section choose Conditional Formatting > Highlight Cells Rules > Duplicate Values. If you want to check for any duplicate data in your Excel file before deleting it, follow this procedure: