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.
#EXCEL FORMULA TO REMOVE DUPLICATES FROM AN ARRAY 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.
0 kommentar(er)
