What is the excel formula to remove duplicates

What is the excel formula to remove duplicates code#

cell F21 contains the formula =MATCH(A21,E$21:E$30,0).ДЇinally we use the INDEX function to list the data elements in sorted order: e.g. This is accomplished using the MATCH function: e.g. since the index of the first occurrence of lion (namely 1) has rank 8 we put a 1 in the 8th position of column F (i.e. Note that all the elements in the list now have a different ranking and the blank entry has a rank of 0.ДЇor each data element (from column B) we need to determine its rank (from column E) and put the index of that element (from column A) in the row of E with that rank. We add these count values from column D to the rank values from column C to obtain a revised ranking in column E. cell D21 contains the formula =COUNTIF(B$21:B21,B21). We next count the number of occurrences of each word in the list (column D): e.g. Note that duplicate entries for ape have the same rank (namely 0) and similarly the duplicate entries for dog and lion have the same ranking (4 and 7 respectively). We rank each of the elements in column B from lowest in alphabetic order to highest in column C: e.g. This time we need to use the COUNTIF, MATCH and INDEX functions to accomplish our goal (see Built-in Excel Functions). The approach used in Example 1 doesnвЂ™t work with non-numeric data. We can also remove the #NUM! entries using formulas such as =IF(ISNUMBER(P6),P6,вЂќвЂќ).Д®xamples 3: Sort the alphanumeric data in column B of Figure 3.ДЇigure 3 вЂ“ Sorting alphanumeric data using Excel formulas We can combine some of the steps as shown in the right side of Figure 2.

What is the excel formula to remove duplicates code#

Note that the extra cells are filled with the error code #NUM!. We next sort the resulting elements as we did in Example 1. We do this by placing =IF(J6=0,I6,вЂќвЂќ) in cell K6 and filling down.Д¬olumn K now contains the same elements as in column I but without duplicates. We now want to retain those elements whose count is 0 in column J. To eliminate duplicates we need to count the number of times each data item in column I occurs (column J) by placing the formula =COUNTIF(I7:I$16,вЂќ=вЂќ&I6) in cell J6, highlighting range J6:J15 and pressing Ctrl-D. 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.