Find and remove duplicates - Revision history

Planetoid: /* Marking Duplicate Titles with Sequential Numbers */

2026-01-06T06:32:42Z

Marking Duplicate Titles with Sequential Numbers

← Older revision		Revision as of 14:32, 6 January 2026
Line 21:		Line 21:
	{\| class="wikitable"		{\| class="wikitable"
	\|-		\|-
	! Row !! Title !! Formula (Sequential) !! Formula Result (Sequential) !! Formula Result (Flag Only)		! Row !! Title !! Formula (Sequential) !! Formula Result (Sequential) !! Formula (Flag Only) !! Formula Result (Flag Only)
	\|-		\|-
	\| A2 \|\| Duplicate Title \|\| =COUNTIF($A$2:A2, A2)-1 \|\| 0 \|\| (blank)		\| 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:A2, A3)-1 \|\| 0 \|\| (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:A2, A4)-1 \|\| 1 \|\| 1		\| 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:A2, A5)-1 \|\| 2 \|\| 1		\| A5 \|\| Duplicate Title \|\| =COUNTIF($A$2:A5, A5)-1 \|\| 2 \|\| =IF(COUNTIF($A$2:A5, A5)>1, 1, "") \|\| 1
	\|}		\|}

Planetoid: /* Finding Duplicate Rows That Differ in One Column */

2026-01-06T06:29:47Z

Finding Duplicate Rows That Differ in One Column

@@ Line 15: / Line 15: @@
 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 ====

Planetoid at 07:25, 5 November 2025

2025-11-05T07:25:54Z

← Older revision		Revision as of 15:25, 5 November 2025
Line 209:		Line 209:
	{{Template:Data factory flow}}		{{Template:Data factory flow}}

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

Planetoid: /* Find duplicates in Excel */

2025-11-05T07:25:25Z

Find duplicates in Excel

← Older revision		Revision as of 15:25, 5 November 2025
Line 20:		Line 20:

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

Planetoid at 03:34, 15 July 2025

2025-07-15T03:34:50Z

← Older revision		Revision as of 11:34, 15 July 2025
Line 182:		Line 182:
	== References ==		== References ==
	<references/>		<references/>


			{{Template:Data factory flow}}

	[[Category:Spreadsheet]] [[Category:Excel]]		[[Category:Spreadsheet]] [[Category:Excel]]

Planetoid: /* Find and remove duplicates in Excel */

2024-03-19T06:54:22Z

Find and remove duplicates in Excel

← Older revision		Revision as of 14:54, 19 March 2024
Line 1:		Line 1:
	== Find and remove duplicates in Excel ==		== Find and remove duplicates in Excel ==
	=== Find duplicates in Excel ===		=== Find duplicates in Excel ===
	Finding ~~duplicate rows that differ~~ in ~~one column~~		==== Finding Duplicate Rows That Differ in One Column ====
	* one column data: [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~~		Example function
			<pre>
			=IF(AND(LEN(A2)>0, COUNTIF(A:A, A2)>=2), 1, 0)
			</pre>

	Finding duplicate rows that differ in multiple columns		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)}}.		* 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)}}.

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

	=== Remove duplicates in Excel ===		=== 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}}		* 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 ==		== Find and remove duplicates in Cygwin/BASH ==

Planetoid: Text replacement - "[[Alternative_Linux_commands" to "[[Linux commands"

2024-02-16T05:53:13Z

Text replacement - "[[Alternative_Linux_commands" to "[[Linux commands"

← Older revision		Revision as of 13:53, 16 February 2024
Line 24:		Line 24:

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

	case 1: To remove duplicate lines of entire paragraph		case 1: To remove duplicate lines of entire paragraph

Unknown user: /* Find and remove duplicates in Google Spreadsheet */

2023-10-04T09:04:00Z

Find and remove duplicates in Google Spreadsheet

← Older revision		Revision as of 17:04, 4 October 2023
Line 127:		Line 127:
	* [https://sheetaki.com/remove-duplicates-from-comma-delimited-strings-in-google-sheets/ Remove Duplicates from Comma-Delimited Strings in Google Sheets - Sheetaki]		* [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 {{kbd \| key=<nowiki>", "</nowiki>}} (a comma followed by a space):		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>		<pre>
	apple, banana, apple		apple, banana, apple
Line 134:		Line 134:
	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>)		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>		<pre>
			If cell A2's content is separated by a comma or whitespace:
	= JOIN(", ",UNIQUE(TRANSPOSE(SPLIT(A2,", "))))		= JOIN(", ",UNIQUE(TRANSPOSE(SPLIT(A2,", "))))

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

Unknown user: /* Find and remove duplicates in Google Spreadsheet */

2023-10-03T09:46:42Z

Find and remove duplicates in Google Spreadsheet

← Older revision		Revision as of 17:46, 3 October 2023
Line 132:		Line 132:
	</pre>		</pre>

	To remove duplicates from Cell A2, use the following formula:		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>		<pre>
	=JOIN(", ",UNIQUE(TRANSPOSE(SPLIT(A2,", "))))		= JOIN(", ",UNIQUE(TRANSPOSE(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>		</pre>

Unknown user: /* Find and remove duplicates in Google Spreadsheet */

2023-10-03T09:20:41Z

Find and remove duplicates in Google Spreadsheet

@@ Line 125: / Line 125: @@
 ** 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]
 == Find and remove duplicates in PHP ==