Editing Find and remove duplicates

== Find and remove duplicates in Excel ==
=== 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

==== 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]

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

== Find and remove duplicates in Cygwin/BASH ==
=== Finding duplicate values in Cygwin/BASH ===
* [https://www.computerhope.com/unix/uuniq.htm uniq command] on Cygwin of {{Win}} or {{Linux}}: {{kbd | key=<nowiki>uniq -d <file.txt> > <duplicated_items.txt></nowiki>}}<ref>[https://unix.stackexchange.com/questions/52534/how-to-print-only-the-duplicate-values-from-a-text-file shell - How to print only the duplicate values from a text file? - Unix & Linux Stack Exchange]</ref>


=== Remove duplicate values in Cygwin/BASH ===

[http://www.gnu.org/software/coreutils/manual/html_node/sort-invocation.html GNU Coreutils: sort invocation] OS: {{Linux}}, cygwin of {{Win}}.

Data preparation:
* [[Linux commands#Merge_multiple_plain_text_files | Merge multiple plain text files]]

case 1: To remove duplicate lines of entire paragraph
* {{kbd | key=<nowiki>sort -us -o <output_unique.file> <input.file></nowiki>}} in a large text file (GB)<ref>[http://unix.stackexchange.com/questions/19641/how-to-remove-duplicate-lines-in-a-large-multi-gb-textfile linux - How to remove duplicate lines in a large multi-GB textfile? - Unix & Linux Stack Exchange]</ref> 
* {{kbd | key=<nowiki>cat <input.file> | grep <pattern> | sort | uniq</nowiki>}} Processes text line by line and prints the '''unique''' lines which match a specified pattern. Equal to these steps: (1) {{kbd | key=<nowiki>cat <input.file> | grep <pattern> > <tmp.file></nowiki>}} (2) {{kbd | key=<nowiki>sort <tmp.file> | uniq</nowiki>}}

Case 2: Ignore first n line(s) & remove duplicate lines<ref>[https://stackoverflow.com/questions/14562423/is-there-a-way-to-ignore-header-lines-in-a-unix-sort sorting - Is there a way to ignore header lines in a UNIX sort? - Stack Overflow]</ref><ref>[http://linux.vbird.org/linux_basic/0320bash.php#redirect_com 命令執行的判斷依據： ; , &&, ||]</ref><ref>[https://www.computerhope.com/unix/utail.htm Linux tail command help and examples]</ref>
* (1) ignore first one line: {{kbd | key=<nowiki>(head -n 1 <file> && tail -n +2 <file> | sort -us) > newfile</nowiki>}}  
* (2) ignore first two lines: {{kbd | key=<nowiki>(head -n 2 <file> && tail -n +3 <file> | sort -us) > newfile</nowiki>}}

== Find and remove duplicates in MySQL ==
=== Find duplicates in MySQL ===
==== Finding duplicate value that differ in one column ====
'''Finding duplicate value that differ in one column'''<ref>[http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql?rq=1 Finding duplicate values in MySQL - Stack Overflow]</ref>
<pre>
-- Generate test data.
CREATE TABLE `table_name` (
  `id` int(11) NOT NULL,
  `content` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `table_name` (`id`, `content`) VALUES
(1, 'apple'),
(2, 'lemon'),
(3, 'apple');

ALTER TABLE `table_name`
  ADD PRIMARY KEY (`id`);

-- Find duplicated data
SELECT `content`, COUNT(*) count 
FROM `table_name` 
GROUP BY `content` 
HAVING count > 1;

SELECT tmp.* FROM 
( 
  SELECT `content`, count(*) count FROM `table_name` GROUP BY `content` 
) tmp 
WHERE tmp.count >1;
</pre>

'''Another approach:''' Add {{kbd | key=content_hash}} column which its value is {{kbd | key=MD5(content_column)}}<ref>[https://errerrors.blogspot.com/2023/08/how-to-find-duplicate-text-values-in-mysql.html MySQL 如何尋找重複的文章 (文字類型資料)]</ref>

<pre>
-- To find the duplicated records with same content
SELECT count(*) as count, content_hash
FROM `YOUR_TABLE_NAME`
GROUP BY content_hash
HAVING count >= 2
</pre>

==== Finding duplicate rows that differ in multiple columns ====
Finding duplicate rows that differ in multiple columns: Using {{kbd | key =CONCAT}} for multiple columns ex: column_1, column_2 
<pre>
SELECT count(*) count, CONCAT(  `column_1`, `column_2`  ) 'key'
	FROM `table_name`
	GROUP BY CONCAT(  `column_1`, `column_2`  )
HAVING count > 1;
</pre>

or
<pre>
SELECT tmp.key FROM
(
	SELECT count(*) count, CONCAT(  `column_1`, `column_2`  ) 'key'
	FROM `table_name`
	GROUP BY CONCAT(  `column_1`, `column_2`  )
) tmp
WHERE tmp.count >=2
</pre>

==== Select deduplicated/duplicated records ====
* [http://www.mysqltutorial.org/mysql-distinct.aspx MySQL DISTINCT - Eliminate Duplicate Rows in a Result Set]. Using {{kbd | key =GROUP_CONCAT}} to handle the multiple columns<ref>[http://stackoverflow.com/questions/12188027/mysql-select-distinct-multiple-columns sql - MySQL SELECT DISTINCT multiple columns - Stack Overflow]</ref>
* [http://www.w3schools.com/sql/sql_unique.asp SQL UNIQUE Constraint] "Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table." Quoted from w3schools webpage.
* "{{kbd | key = UNION}} removes duplicates, whereas {{kbd | key = UNION ALL}} does not." source: [http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all sql - What is the difference between UNION and UNION ALL? - Stack Overflow]


For counting purpose: find the count of repeated id (type: int) between table_a and table_b
<pre>
SELECT count(DISTINCT(id)) FROM table_a WHERE id IN
(
   SELECT DISTINCT(id) FROM table_b
)
</pre>

Counting number of duplicate occurrence
MySQL: find the number of duplicate occurrence between list_a & list_b which using the same primary key: column name {{kbd | key = id}}
* {{kbd | key = SELECT count(DISTINCT(`id`)) FROM `list_a` WHERE `id` IN (SELECT DISTINCT(`id`) FROM `list_b`) ; }}

=== Remove duplicates in MySQL ===

* [http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql sql - Delete all Duplicate Rows except for One in MySQL? - Stack Overflow]


== Find and remove duplicates in Google Spreadsheet ==

* [https://www.ablebits.com/google-sheets-add-ons/remove-duplicates/index.php Remove duplicates in Google Sheets] 30 days free {{access | date = 2019-02-26}}
* [https://chrome.google.com/webstore/detail/power-tools/dofhceeoedodcaheeoacmadcpegkjobi Power Tools] for Google Spreadsheet {{access | date = 2019-02-26}}
** Menu: Data -> Remove duplicates
* Google spreadsheet add-on: [https://www.ablebits.com/google-sheets-add-ons/remove-duplicates/howto.php Remove Duplicates for Google Sheets help]
* [https://sheetaki.com/remove-duplicates-from-comma-delimited-strings-in-google-sheets/ Remove Duplicates from Comma-Delimited Strings in Google Sheets - Sheetaki]

Initial contents of cell A2 which its separator is comma or whitespace. The second parameter of SPLIT function is {{kbd | key=<nowiki>", "</nowiki>}} (a comma followed by a space):
<pre>
apple, banana, apple
</pre>

To remove duplicates from Cell A2, use the following formula: ((learn more about the UNIQUE function from Google Help<ref>[https://support.google.com/docs/answer/10522653?hl=zh-Hant UNIQUE 函式 - Google 文件編輯器說明]</ref> or Microsoft support<ref>[https://support.microsoft.com/zh-tw/office/unique-%E5%87%BD%E6%95%B8-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e UNIQUE 函數 - Microsoft 支援服務]</ref>)
<pre>
If cell A2's content is separated by a comma or whitespace:
= JOIN(", ",UNIQUE(TRANSPOSE(SPLIT(A2,", "))))

If cell A2's content is separated solely by a comma:
= JOIN(", ", UNIQUE(TRANSPOSE(ARRAYFORMULA(TRIM(SPLIT(A2, ","))))))
</pre>

Alternatively, activate the `by_column=true` parameter in the second argument of the UNIQUE function:
<pre>
= JOIN(", ",UNIQUE(SPLIT(A2,", "), 1)))
</pre>

Cell A2's contents after removing duplicates:
<pre>
apple, banana
</pre>

== Find and remove duplicates in PHP ==
=== Find duplicates in PHP ===
* PHP [http://php.net/manual/en/function.array-intersect.php PHP: array_intersect] function

=== Remove duplicates in PHP ===
* PHP: [http://php.net/manual/en/function.array-unique.php PHP: array_unique] function

== Find and remove duplicates in JavaScript ==
=== Remove duplicates in JavaScript ===
* JavaScript: [https://www.delftstack.com/howto/javascript/javascript-remove-duplicates-from-an-array/ Remove Duplicates From an Array in JavaScript | Delft Stack]



== Other issues ==
* symbol e.g. data-mining or data_mining

== Related articles ==
* [https://ithelp.ithome.com.tw/questions/10210232 【PYTHON】請問如何避免內容重複抓取 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天]

== References ==
<references/>


{{Template:Data factory flow}}

[[Category:Spreadsheet]] [[Category:Excel]]
[[Category:Data_hygiene]]
[[Category:Data Science]]
[[Category:MySQL]]
[[Category:String manipulation]]