excel formula to remove duplicates from list

This time we put the formula =SMALL(E$6:E$15,ROW(E6)-ROW(E$6)+1) in cell F6, highlight F6:F15 and press Ctrl-D.Įxample 2: Sort the data from Example 1 dropping any duplicate entries. The column of indices (column A) is not really necessary since the same effect can be achieved using the ROW function (see column F of Figure 1). If any of the cells is empty or contains non-numeric data it will not be sorted, but instead the error value #NUM! will appear at the end of the sorted list in column C. This approach only works for numeric data.

Sorting is achieved since the formula =SMALL(B$4:B$13, d) has the value of the dth smallest element in the range B6:B15). Column C now contains the same data as in column B but in sorted order. Next place the formula =SMALL(B$6:B$15,A4) in cell C6, highlight the range C6:C15 and press Ctrl-D (to fill down). You can also use Excel formulas to sort a column of data, as described in the following example.Įxamples 1: Sort the numeric data in column B of Figure 1.įigure 1 – Sorting numeric data using standard Excel formulasĬreate a column of indices in column A by putting 1 in cell A6, the formula =A6+1 in cell A7 and then filling down (by highlighting the range A7:A15 and pressing Ctrl-D). See Sorting and Filtering for more details. These can be accessed by Data > Sort & Filter|Sort and Data > Data Tools|Remove Duplicates. Excel provides standard capabilities for sorting and eliminating duplicates.

Excel formula to remove duplicates from list