Step 4: Click dropdown list arrow button, only check on value 1 in the list. So we can use filter function in B column to filter them out. Step 3: We can see that for the duplicate values, they are marked with 1 in B column. ![]() ![]() Due to some values are duplicate in the list, you can see for some values IF function returns 0, and for the others returns 1. ![]() So B2 to B9 will be filled by this formula. Step 2: Drag the fill handle down to fill the other cells. For example, A2 value Ada only appears once in the list, so COUNTIF($A$2:$A$9,A2) will return 1 (so COUNTIF($A$2:$A$9,A2)=1 is true), so IF function will return 0 in B2. COUNTIF function is used for counting the number of A2 from range $A$2:$A$9, and IF function is used for returning True of False value. In this formula we use IF and COUNTIF functions together. ![]() A2) to see if there are duplicates and delete them For instance. Step 1: In cell B2 enter the formula =IF(COUNTIF($A$2:$A$9,A2)=1,0,1). Can Excel formula evaluate the string in a cell (e.g. If you want to remove both duplicate values like ‘Amy’ ‘Cathy’, you can follow below steps. Whether it's an import error or someone accidentally pasting something twice, duplicate data makes your spreadsheets less useful. Original List: Updated List after Remove Duplicates:
0 Comments
Leave a Reply. |