Introduction of five methods for Excel to eliminate duplicate values of single column data

Sometimes we are operating data, we need to eliminate the duplicate value of the single row of data, the following small series for everyone to introduce Excel to eliminate a single row of data duplicate values of five methods to meet the daily needs of everyone.

Method One: Menu button

The following figure is the source data for this operation.

Click the Data tab--the Data Tools ribbon--remove duplicates, pop the Delete Duplicates dialog box, and click OK to delete a single-column data duplicate value. As shown in the following illustration:

　　Method Two: Pivot Table method

Still use the data source above, click the Insert tab--"Table ribbon--" PivotTable report, the following illustration appears, where I select the C1 cell of the existing worksheet (you can select a new worksheet as needed), and click OK to finish creating the PivotTable report. The following figure:

Next, tick the checkbox in front of name, and the Name field appears in the box in the Line field, as shown in the following figure:

Then, we decorate the PivotTable data, click the cell that contains the row label, change the cell's text to name, and then click any cell in the PivotTable report, click the PivotTable Tool--Design tab--the Layout ribbon--"total." The row and column disabled button is complete, as shown in the following illustration:

　　Method Three: Formula method

As shown in the figure, enter the following formula in cell C1, then press ctrl+shift+enter at the end of the three key, and then drag the fill handle in the lower-right corner of the formula cell to complete the filter for this data.

Then we explain the formula step-by-step, first of all, MATCH ($A $ $A $, $A $ $A $) =row ($1:$10) to find A1 to A10 cell in the reference area $a$1: $A The position of $ $ is equal to the location of the current cell cells number. If equal, the data is unique in this area, and then the row number of the field is returned through the IF (MATCH) composition function, otherwise the 2^20=1048576 is returned, then the fetched line number is sorted in ascending order with the small function, and finally the value of the line number is found through the index function ,& "" mainly for fault-tolerant processing, just imagine, if the data are finished, there are 1048576 of the position, and then index (a:a,1048576) = 0, add a & "" then return empty text.

　　Method Four: SQL method

Click the Data tab--"Get External Data ribbon--" Existing connection, open the Existing Connection dialog box, click Browse for more in the lower-left corner, and then find the path to the workbook where you manipulated the data source, click Open, the Select Table dialog box appears, leave the default status, and click OK , as shown in the following illustration:

Then we select the "Table" radio button, here I select the C1 cell for the existing worksheet (you can select a new worksheet if you want), click the Properties button, open the Connection Properties dialog box, locate the Definition tab, and then clear the text of the command text and enter the following statement: SELECT DISTINCT * FROM [sheet1$] (here Sheet1 is my source data name, you want to enter the name of your own worksheet oh, and [] and $ can not be less oh, if the range of cells and other fields or data is not the beginning of the line, but also enter your data source area oh, such as [ Sheet1$]. A4:A12), as shown in the following illustration:

　　Method Five: Advanced Screening method

Click the Data tab--sort and filter the ribbon--advanced button to eject the Advanced Filter dialog box, as shown in the following illustration:

Select Copy filter results to another location, select the location of list area and copy to, and check the Select Not Duplicates checkbox, as shown in the following illustration, and click OK to complete the filter for duplicate values.

The above is excel to eliminate a single row of data repeat value five methods introduced, we can try, hands-on experience oh.