Excel operation content to remove duplicate values ​​from single column data

Method 1: Menu button

As shown in the figure, it is the source data of this operation.

Click the [Data] tab--"[Data Tools] Ribbon--"[Delete Duplicates], the [Delete Duplicates] dialog box will pop up, click [OK] 】to delete duplicate values ​​in a single column of data.

Method 2: Pivot table method

Still using the above data source, click the [Insert] tab--"[Table] ribbon --》[Pivot Table], the prompt box as shown in the figure will appear. Here, select cell C1 of the existing worksheet (you can select a new worksheet as needed), click [OK] to complete the creation of the Pivot Table, as follows Picture:

Check the check box in front of [Name], and the [Name] field will appear in the frame of the "Row" field, as shown in the picture:

Then modify the data in the pivot table, click the cell where [row label] is located, change the text of the cell to [name], click any cell in the pivot table, and click [Pivot Table Tool]--"[Design] tab--"[Layout] ribbon--"[Disable rows and columns] button under [Total] is completed, as shown in the figure:

Method Three: Formula Method

As shown in the figure, enter the following formula in cell C1, press Ctrl Shift Enter three keys at the same time to end, drag the cell to enter the formula The fill handle in the lower right corner completes the filtering of non-duplicate data.

Then we will explain the formula step by step. First, MATCH($A$1:$A$10,$A$1:$A$10,)=ROW($1: $10) means to find whether the position of cells A1 to A10 in the reference area $A$1:$A$10 is equal to the position of the current cell row number. If they are equal, it means that the data is unique in this area, and then through IF(MATCH ()) The combination function returns the row number of this field, otherwise it returns 2^20=1048576, then uses the SMALL function to sort the obtained row numbers in ascending order, and finally uses the INDEX function to find the value of the position of the row number, &[] mainly For fault tolerance, imagine that if all the data is fetched, only 1048576 positions are left, then INDEX(A:A,1048576)=0, add a &[] to return empty text.

Method 4: SQL method

Click the [Data] tab--""Get External Data" ribbon--"Existing Connection, open the [Existing Connection] dialog box, Click [Browse More] in the lower left corner, then find the path to the workbook where the data source is located, click [Open], and the [Select Table] dialog box will appear. Keep the default state, and click the [OK] button, such as As shown in the picture:

Then select the [Table] radio button. Here I select cell C1 of the existing worksheet (you can select a new worksheet as needed), Click the [Properties] button to open the "Connection Properties" dialog box, find the [Definition" tab, clear the text of the command text, and enter the following statement: SELECT DISTINCT * FROM [Sheet1$] (Here Sheet1 is my source data Name, enter the name of your worksheet, and [] and $ cannot be missing. If there are other fields in the cell area or the data is not at the beginning of the row, you must also enter your data source area, such as [Sheet1$].A4 :A12), as shown in the figure:

Method Five: Advanced Filtering Method

Click the [Data] tab--》[ Sort and Filter] Ribbon--"[Advanced] button, the [Advanced Filtering] dialog box will pop up, as shown in the figure:

Select [Copy filter results to other locations], select [List area] and [Copy to] locations, and check the [Select non-duplicate records] checkbox. As shown in the figure, click [OK] to complete the filtering of duplicate values.

The above is the detailed content of Excel operation content to remove duplicate values ​​from single column data. For more information, please follow other related articles on the PHP Chinese website!