![]() In the non-array formula part, we have used one Countif formula in cell C2. Array Formula to Remove Duplicate Values without Deleting the Rows So better to use my formula in a sorted list in Google Sheets. The formula would return the unique values, but not worthy for formatting purpose. This will make the list an unsorted one.Ĭheck the result in C2:C14. Just select B2:B14 and click Data > Randomize range. What about an unsorted list with the above Countif formula in Google Sheets? That’s all that you want to do to remove duplicate values without deleting the rows in Google Sheets.Īs you can see in the example, the list in B2:B14 is sorted. The above is the present formula in cell B2. If the value is 1, return the name from cell B2, else a blank. Use the IF logical with the cell C2 formula to determine whether the value in the cell C2 is 1 or greater than 1. The result will be the cumulative count of the values in the list as below in C2:C14.ģ. To know the use of the above Countif formula, you may drag the fill handle in cell C2 (a blue square at the bottom right-hand side of the active cell) down. In cell C1 use the Countif formula =countif($B$2:B2,B2) to get the count of the first item in the range $B$2:B2.Ģ. Non-Array Formula to Delete Duplicates But Keep the Corresponding Rowsġ. Here our list is in B2:B14 in which we are going to apply the non-array formula first. To remove duplicate values without deleting the corresponding rows in Google Sheets, as I have mentioned at the very beginning, we can use a non-array formula or array formula. Remove Duplicate Values without Deleting the Rows But the topic that we are going to discuss in this tutorial is nothing to do with data cleaning. So for better data analysis, clean your data. Other than the above a ‘Smart Cleanup’, a new Google Sheets feature, is also coming ( source). The commands are Split text to columns, Remove duplicates, and Trim white space. Regarding menu commands for the same purpose, then we can look for a few options under the Data menu. In Google Sheets, Clean, Unique, and Sortn are some of the functions that are quite useful for cleaning data. It is important to clean data for better data analysis. If you want to categorize the topic of this tutorial, you can consider it under data formatting, not data cleaning. We can use two types of formulas (array or non-array) to remove duplicate values without deleting the corresponding rows in Google Sheets. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |