14,970
edits
Find and remove duplicates: Difference between revisions
Jump to navigation
Jump to search
→Find and remove duplicates in Excel
m (Text replacement - "[[Alternative_Linux_commands" to "[[Linux commands") Tags: Mobile edit Mobile web edit |
|||
| Line 1: | Line 1: | ||
== Find and remove duplicates in Excel == | == Find and remove duplicates in Excel == | ||
=== Find duplicates in Excel === | === Find duplicates in Excel === | ||
Finding | ==== Finding Duplicate Rows That Differ in One Column ==== | ||
Example function | |||
<pre> | |||
=IF(AND(LEN(A2)>0, COUNTIF(A:A, A2)>=2), 1, 0) | |||
</pre> | |||
Finding duplicate rows that differ in multiple columns | This function works by checking each cell in a specified column (in this case, column A). Here's a breakdown of how it operates: | ||
* LEN(A2)>0: This part of the formula checks if the cell is not empty. It prevents empty cells from being flagged as duplicates. | |||
* COUNTIF(A:A, A2)>=2: This checks how many times the value in cell A2 appears in column A. If it appears two or more times, it meets the criteria for being considered a duplicate. | |||
* IF(AND(...), 1, 0): The IF function combined with AND evaluates the conditions mentioned above. If both conditions are true (meaning the cell is not empty and the value appears two or more times), the function returns 1, indicating a duplicate. If not, it returns 0. | |||
References | |||
* [http://www.extendoffice.com/documents/excel/1499-count-duplicate-values-in-column.html How to count duplicate values in a column in Excel?] Using {{kbd | key = COUNTIF(range, criteria)}} {{access | date = 2015-08-25}} or using '''Pivot Tables'''(樞紐分析表) to find the occurrence of value >= 2 | |||
==== Finding duplicate rows that differ in multiple columns ==== | |||
* two or multiple columns data: (approach 1) [https://support.microsoft.com/en-us/kb/213367 How to compare data in two columns to find duplicates in Excel] {{access | date = 2015-06-16}} {{exclaim}} It may costs too much time (larger than one hour) if the number of records exceeds 1,000,000 (approach 2) Using [https://support.office.com/en-us/article/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2 CONCAT function] to concatenate two or multiple columns data. And then use {{kbd | key = COUNTIF(range, criteria)}}. | * two or multiple columns data: (approach 1) [https://support.microsoft.com/en-us/kb/213367 How to compare data in two columns to find duplicates in Excel] {{access | date = 2015-06-16}} {{exclaim}} It may costs too much time (larger than one hour) if the number of records exceeds 1,000,000 (approach 2) Using [https://support.office.com/en-us/article/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2 CONCAT function] to concatenate two or multiple columns data. And then use {{kbd | key = COUNTIF(range, criteria)}}. | ||
* [http://superuser.com/questions/307837/how-to-count-number-of-repeat-occurrences microsoft excel - How to count number of repeat occurrences - Super User] {{exclaim}} long number issue: [https://superuser.com/questions/783840/countif-incorrectly-matches-long-number microsoft excel - Countif incorrectly matches long number - Super User] | * [http://superuser.com/questions/307837/how-to-count-number-of-repeat-occurrences microsoft excel - How to count number of repeat occurrences - Super User] {{exclaim}} long number issue: [https://superuser.com/questions/783840/countif-incorrectly-matches-long-number microsoft excel - Countif incorrectly matches long number - Super User] | ||
=== Remove duplicates in Excel === | === Remove duplicates in Excel === | ||
* EXCEL: Data Tools -> Remove Duplicates: [https://support.office.com/en-us/article/Filter-for-unique-values-or-remove-duplicate-values-d6549cf0-357a-4acf-9df5-ca507915b704 Filter for unique values or remove duplicate values] {{access | date = 2015-10-20}} | * EXCEL: Data Tools -> Remove Duplicates: [https://support.office.com/en-us/article/Filter-for-unique-values-or-remove-duplicate-values-d6549cf0-357a-4acf-9df5-ca507915b704 Filter for unique values or remove duplicate values] {{access | date = 2015-10-20}} | ||
== Find and remove duplicates in Cygwin/BASH == | == Find and remove duplicates in Cygwin/BASH == | ||