![excel formula to remove duplicates from a list excel formula to remove duplicates from a list](https://cdn.extendoffice.com/images/stories/doc-excel/remove-duplicate-keep-rest/doc-remove-duplicates-keep-rest-4.png)
![excel formula to remove duplicates from a list excel formula to remove duplicates from a list](https://pryormediacdn.azureedge.net/blog/2015/09/FindDuplicates01.png)
=IF(COUNTIF($B$2:B3,B3)=1, A2+1, A2) If COUNTIF returns 1, which means the value in cell B3 is unique in column B till the current row, then formula increments cell A3 by 1 from cell A2, else cell A3 value remains same as cellA2. The relative reference (B3) is used to keep the last cell in the range dynamic, till the current row. The absolute reference ($B$2) is used to start the search from the first cell in the range/column. Please note the use of absolute and relative references for the range $B$2:B3. Paul Spencer) appears in column B, till the current row (viz. Explaining the formula in cell A3 (refer Table 1): =IF(COUNTIF($B$2:B3,B3)=1,A2+1,A2) =COUNTIF($B$2:B3,B3) Counts the number of times the value in cell B3 (viz. column A, as its purpose has been served by now. The formula does a vlookup of running values starting from 1, searches for corresponding results in column B, and returns unique values in column C. =IFERROR( VLOOKUP(ROW()-1, $A$2:$B$16,2, FALSE),"") -> Enter this formula in cell C2 and copy it down column C. The formula counts the number of names in column B till the current row, and increments the number in column A (from the upper cell) if the name has occurred only once till that row, else returns the same number as in the upper cell (in column A). You will notice that if the number increments from the previous/upper cell in column A, then the corresponding record in column B is unique. Enter the formula =IF(COUNTIF( $B$2:B3,B3)=1, A2+1, A2) in cell A3 and copy it down column A. Enter 1 in the first cell corresponding to the first name in column B. Create a helper Column towards the left viz. Column B contains a list of names, including duplicates and a List of Unique Names is returned in column C, with the creation of the helper column (column A). Related Links: Count Number of Unique Values, with Excel Functions Remove Duplicates or Create a List of Unique Records Using Advanced Data Filter Remove Duplicates in a range, using "Remove duplicates" button in Data Tools - Remove Duplicates or Create a List of Unique Records in Excel Create a List of Unique Records (Using Excel Formula): 1.