Find and remove duplicates

From LemonWiki共筆
Jump to navigation Jump to search

Find and remove duplicates in Excel

Find duplicates in Excel

Finding Duplicate Rows That Differ in One Column

Example function 

=IF(AND(LEN(A2)>0, COUNTIF(A:A, A2)>=2), 1, 0)

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

Finding duplicate rows that differ in multiple columns


Checking for Duplicate Values in a Specified Range

Example function 

=SUMPRODUCT(COUNTIF(E2:N2, E2:N2)) > COUNTA(E2:N2)

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 SUMPRODUCT(COUNTIF(E2:N2, E2:N2) - 1) to calculate the number of duplicate items
  • Using UNIQUE function: Using COUNTA(E2:N2) <> COUNTA(UNIQUE(E2:N2)) as a simplified version (Google Sheets only)

References

Remove duplicates in Excel

Find and remove duplicates in Cygwin/BASH

Finding duplicate values in Cygwin/BASH


Remove duplicate values in Cygwin/BASH

GNU Coreutils: sort invocation OS: Linux   , cygwin of Win   .

Data preparation:

case 1: To remove duplicate lines of entire paragraph

  • sort -us -o <output_unique.file> <input.file> in a large text file (GB)[2]
  • cat <input.file> | grep <pattern> | sort | uniq Processes text line by line and prints the unique lines which match a specified pattern. Equal to these steps: (1) cat <input.file> | grep <pattern> > <tmp.file> (2) sort <tmp.file> | uniq

Case 2: Ignore first n line(s) & remove duplicate lines[3][4][5]

  • (1) ignore first one line: (head -n 1 <file> && tail -n +2 <file> | sort -us) > newfile
  • (2) ignore first two lines: (head -n 2 <file> && tail -n +3 <file> | sort -us) > newfile

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

-- 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;

Another approach: Add content_hash column which its value is MD5(content_column)[7] 

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

Finding duplicate rows that differ in multiple columns

Finding duplicate rows that differ in multiple columns: Using CONCAT for multiple columns ex: column_1, column_2 

SELECT count(*) count, CONCAT(  `column_1`, `column_2`  ) 'key'
	FROM `table_name`
	GROUP BY CONCAT(  `column_1`, `column_2`  )
HAVING count > 1;

or 

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

Select deduplicated/duplicated records


For counting purpose: find the count of repeated id (type: int) between table_a and table_b 

SELECT count(DISTINCT(id)) FROM table_a WHERE id IN
(
   SELECT DISTINCT(id) FROM table_b
)

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 id

  • SELECT count(DISTINCT(`id`)) FROM `list_a` WHERE `id` IN (SELECT DISTINCT(`id`) FROM `list_b`) ;

Remove duplicates in MySQL


Find and remove duplicates in Google Spreadsheet

Initial contents of cell A2 which its separator is comma or whitespace. The second parameter of SPLIT function is ", " (a comma followed by a space): 

apple, banana, apple

To remove duplicates from Cell A2, use the following formula: ((learn more about the UNIQUE function from Google Help[9] or Microsoft support[10]) 

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, ","))))))

Alternatively, activate the `by_column=true` parameter in the second argument of the UNIQUE function: 

= JOIN(", ",UNIQUE(SPLIT(A2,", "), 1)))

Cell A2's contents after removing duplicates: 

apple, banana

Find and remove duplicates in PHP

Find duplicates in PHP

Remove duplicates in PHP

Find and remove duplicates in JavaScript

Remove duplicates in JavaScript


Other issues

  • symbol e.g. data-mining or data_mining

Related articles

References

  1. ↑ shell - How to print only the duplicate values from a text file? - Unix & Linux Stack Exchange
  2. ↑ linux - How to remove duplicate lines in a large multi-GB textfile? - Unix & Linux Stack Exchange
  3. ↑ sorting - Is there a way to ignore header lines in a UNIX sort? - Stack Overflow
  4. ↑ 命令執行的判斷依據： ; , &&, ||
  5. ↑ Linux tail command help and examples
  6. ↑ Finding duplicate values in MySQL - Stack Overflow
  7. ↑ MySQL 如何尋找重複的文章 (文字類型資料)
  8. ↑ sql - MySQL SELECT DISTINCT multiple columns - Stack Overflow
  9. ↑ UNIQUE 函式 - Google 文件編輯器說明
  10. ↑ UNIQUE 函數 - Microsoft 支援服務


Data factory flow

Retrieved from "https://wiki.planetoid.info/index.php?title=Find_and_remove_duplicates&oldid=26078"