Guides › How to Remove Duplicate Lines from a List (Excel, Sheets, Notepad and Online)
How to Remove Duplicate Lines from a List (Excel, Sheets, Notepad and Online)
Duplicate lines creep into every list: exports from two systems, merged mailing lists, keyword research, logs. Here is how to remove them in the places you probably already work, and how to get more than a simple “delete repeats”.
What “duplicate” really means
Before you remove anything, decide how strict matching should be. Is “Apple” the same as “apple”? Is “Apple ” with a trailing space the same as “Apple”? Most tools treat those as different unless told otherwise, which is why a list can still look full of duplicates after cleaning. Trimming spaces and ignoring case catches most of them.
Excel
- Remove Duplicates button: select the column, then Data then Remove Duplicates. It keeps the first occurrence of each value and deletes the rest. It ignores letter case.
- UNIQUE function (Excel 365):
=UNIQUE(A2:A500)returns a clean list without touching the original data.
Google Sheets
- Data cleanup: select the range, then Data then Data cleanup then Remove duplicates.
- UNIQUE function:
=UNIQUE(A2:A500)in a new column.
Notepad++
Recent versions have Edit then Line Operations then Remove Duplicate Lines. Older versions only removed consecutive duplicates, so sort the lines first.
The command line
On macOS or Linux, to keep the original order and the first copy of each line:
awk '!seen[$0]++' input.txt > output.txtOr sort and remove in one step with sort -u input.txt. In Windows PowerShell:
Get-Content input.txt | Select-Object -Unique | Set-Content output.txtThat keeps the first occurrence and treats different capitalisation as different lines.
Online, without a spreadsheet
If the list is in a text file, an email, or a web page, the quickest way is to paste it into Remove Duplicates. Choose whether to ignore case, and the cleaned list appears as you type, with a count of how many lines were removed. Nothing is uploaded.
More than “delete the repeats”
Three different goals are easy to confuse:
- Remove duplicates, keep one copy. “A, A, B” becomes “A, B”. This is the usual meaning.
- Keep only the lines that are repeated. “A, A, B” becomes “A”. Useful for finding what appears more than once, such as customers on both of two lists.
- Keep only the lines that appear once. “A, A, B” becomes “B”. Useful when repeated entries should be discarded completely.
The tool supports all three from one setting. For comparing two separate lists instead, see how to compare two columns.
Check your result
After deduplicating, compare the before and after counts. If almost nothing was removed from a list you expected to be full of repeats, the “duplicates” probably differ by spaces or capitals — turn on trimming and case-insensitive matching and run it again.