Find and remove duplicates: Difference between revisions

Jump to navigation Jump to search

Find and remove duplicates (edit)

Revision as of 14:32, 6 January 2026

3,839 bytes added ,  6 January
→‎Marking Duplicate Titles with Sequential Numbers
 
(4 intermediate revisions by the same user not shown)
Line 15: Line 15:
References
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
* [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 ====
==== Finding duplicate rows that differ in multiple columns ====
Line 20: Line 61:


* [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 ===
Line 183: Line 247:
<references/>
<references/>


[[Category:Spreadsheet]] [[Category:Excel]]
 
[[Category:Data_hygiene]]
{{Template:Data factory flow}}
[[Category:Data Science]]
 
[[Category:MySQL]]
[[Category: Spreadsheet]]  
[[Category:String manipulation]]
[[Category: Excel]]
[[Category: Data_hygiene]]
[[Category: Data Science]]
[[Category: MySQL]]
[[Category: String manipulation]]
[[Category: Revised with LLMs]]
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/24595...26167"

Navigation menu