14,727
edits
Find and remove duplicates: Difference between revisions
Jump to navigation
Jump to search
→Find duplicates in Excel
No edit summary |
|||
| Line 20: | Line 20: | ||
* [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] | ||
==== Checking for Duplicate Values in a Specified Range ==== | |||
Example function | |||
<pre> | |||
=SUMPRODUCT(COUNTIF(E2:N2, E2:N2)) > COUNTA(E2:N2) | |||
</pre> | |||
This function checks whether there are duplicate values within the range E2 to N2. Here's a breakdown of how it operates: | |||
* COUNTIF(E2:N2, E2:N2): This part counts how many times each value appears in the range. For each cell, it calculates the occurrence count of that value within the entire range. | |||
* SUMPRODUCT(...): Sums all the count results. If there are no duplicates, the sum will equal the number of cells; if duplicates exist, the sum will be greater than the number of cells. | |||
* COUNTA(E2:N2): Counts the number of non-empty cells in the range. | |||
* ... > COUNTA(E2:N2): Compares the sum with the cell count. If the sum is greater, it indicates duplicates exist and the function returns TRUE; otherwise, it returns FALSE. | |||
Alternative methods | |||
* '''Count duplicate occurrences''': Using {{kbd | key = SUMPRODUCT(COUNTIF(E2:N2, E2:N2) - 1)}} to calculate the number of duplicate items | |||
* '''Using UNIQUE function''': Using {{kbd | key = COUNTA(E2:N2) <> COUNTA(UNIQUE(E2:N2))}} as a simplified version (Google Sheets only) | |||
References | |||
* [https://support.google.com/docs/answer/3093991 Google Sheets COUNTIF function] | |||
* [https://support.google.com/docs/answer/3238496?hl=en&sjid=3385137518767327741-NC SUMIFS function - Google Docs Editors Help] | |||
* [https://support.google.com/docs/answer/10522653?hl=en&sjid=3385137518767327741-NC UNIQUE function - Google Docs Editors Help] | |||
=== Remove duplicates in Excel === | === Remove duplicates in Excel === | ||