Editing Find and remove duplicates (section)

=== Find duplicates in Excel ===
==== Finding Duplicate Rows That Differ in One Column ====
Example function
<pre>
=IF(AND(LEN(A2)>0, COUNTIF(A:A, A2)>=2), 1, 0)
</pre>

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

==== Marking Duplicate Titles with Sequential Numbers ====

'''Practical Example:'''
{| class="wikitable"
|-
! Row !! Title !! Formula (Sequential) !! Formula Result (Sequential) !! Formula (Flag Only) !! Formula Result (Flag Only)
|-
| A2 || Duplicate Title || =COUNTIF($A$2:A2, A2)-1 || 0 || =IF(COUNTIF($A$2:A2, A2)>1, 1, "") || (blank)
|-
| A3 || Other Title || =COUNTIF($A$2:A3, A3)-1 || 0 || =IF(COUNTIF($A$2:A3, A3)>1, 1, "") || (blank)
|-
| A4 || Duplicate Title || =COUNTIF($A$2:A4, A4)-1 || 1 || =IF(COUNTIF($A$2:A4, A4)>1, 1, "") || 1
|-
| A5 || Duplicate Title || =COUNTIF($A$2:A5, A5)-1 || 2 || =IF(COUNTIF($A$2:A5, A5)>1, 1, "") || 1
|}


Example function for marking duplicate occurrence order
<pre>
=COUNTIF($A$2:A2, A2)-1
</pre>

This function marks duplicate entries with sequential numbers (0 for first occurrence, 1 for second, 2 for third, etc.). Here's a breakdown of how it operates:

* $A$2:A2: This is an expanding range with mixed cell references. The starting point ($A$2) is fixed with absolute reference, while the ending point (A2) uses relative reference that changes as the formula is copied down.
* COUNTIF($A$2:A2, A2): This counts how many times the value in the current row appears from the start of the range up to and including the current row. For the first occurrence, the range is just one cell (A2:A2) so it returns 1. For the second occurrence, the range expands (A2:B3) and returns 2.
* -1: Subtracting 1 converts the count to a zero-based index. First occurrence = 1-1 = 0, second occurrence = 2-1 = 1, third occurrence = 3-1 = 2.

'''Marking Only Duplicate Entries'''

Example function for flagging duplicates only
<pre>
=IF(COUNTIF($A$2:A2, A2)>1, 1, "")
</pre>

This function only marks duplicate entries (second occurrence onwards) with 1, leaving first occurrences blank. Here's how it works:

* COUNTIF($A$2:A2, A2)>1: This checks if the current value has appeared more than once in the expanding range from the start up to the current row.
* IF(..., 1, ""): If the condition is true (meaning this is a duplicate), the function returns 1. Otherwise, it returns an empty string (""), leaving the cell blank.
* Result: First occurrence shows nothing, second and subsequent occurrences show 1.

==== 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)}}.

* [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]