Find and remove duplicates

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

• How to count duplicate values in a column in Excel? Using COUNTIF(range, criteria) [Last visited: 2015-08-25] or using Pivot Tables(樞紐分析表) to find the occurrence of value >= 2

Marking Duplicate Titles with Sequential Numbers

Practical Example:

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

=COUNTIF($A$2:A2, A2)-1

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

=IF(COUNTIF($A$2:A2, A2)>1, 1, "")

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) How to compare data in two columns to find duplicates in Excel [Last visited: 2015-06-16] It may costs too much time (larger than one hour) if the number of records exceeds 1,000,000 (approach 2) Using CONCAT function to concatenate two or multiple columns data. And then use COUNTIF(range, criteria).

• microsoft excel - How to count number of repeat occurrences - Super User long number issue: microsoft excel - Countif incorrectly matches long number - Super User

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

• Google Sheets COUNTIF function
• SUMIFS function - Google Docs Editors Help
• UNIQUE function - Google Docs Editors Help

Remove duplicates in Excel

• EXCEL: Data Tools -> Remove Duplicates: Filter for unique values or remove duplicate values [Last visited: 2015-10-20]

Find and remove duplicates in Cygwin/BASH

Finding duplicate values in Cygwin/BASH

• uniq command on Cygwin of Win or Linux : uniq -d <file.txt> > <duplicated_items.txt>

Remove duplicate values in Cygwin/BASH

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

Data preparation:

• Merge multiple plain text files

case 1: To remove duplicate lines of entire paragraph

• sort -us -o <output_unique.file> <input.file> in a large text file (GB)
• 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

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

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

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

• MySQL DISTINCT - Eliminate Duplicate Rows in a Result Set. Using GROUP_CONCAT to handle the multiple columns
• 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.
• "UNION removes duplicates, whereas UNION ALL does not." source: 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

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

• sql - Delete all Duplicate Rows except for One in MySQL? - Stack Overflow

Find and remove duplicates in Google Spreadsheet

• Remove duplicates in Google Sheets 30 days free [Last visited: 2019-02-26]
• Power Tools for Google Spreadsheet [Last visited: 2019-02-26]
  • Menu: Data -> Remove duplicates
• Google spreadsheet add-on: Remove Duplicates for Google Sheets help
• 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 ", " (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 or Microsoft support)

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

• PHP PHP: array_intersect function

Remove duplicates in PHP

• PHP: PHP: array_unique function

Find and remove duplicates in JavaScript

Remove duplicates in JavaScript

• JavaScript: Remove Duplicates From an Array in JavaScript | Delft Stack

Other issues

• symbol e.g. data-mining or data_mining

Related articles

• 【PYTHON】請問如何避免內容重複抓取 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

References

Data factory flow

• 🔍 Data Collection: 1. How to extract content from websites
• 🧹 Data Cleaning: 2. Data cleaning, 3. Regular expression
• ⚙️ Data Processing: 4. Json encode and decode, 5. Convert between date and unix timestamp, 6. MySQL commands
• 📊 Data Analysis: 7. Data exploration
• 📤 Data Output: 8. Comparison of common data file formats, 9. Export MySQL query to Excel file, 10. Microsoft Excel
• 🔧 Troubleshooting: 11. Troubleshooting of MySQL errors