Contents
Method 1. Regular function in Excel 2007
Starting from the 2007 version, the function of removing duplicates is standard – you can find it on the tab Data – Remove Duplicates (Data — Remove Duplicates):
In the window that opens, use the checkboxes to set the columns for which you want to ensure uniqueness. Those. if you enable all checkboxes, then only completely matching rows will be deleted. If you enable only the customer checkbox, then only one row will remain for each customer, and so on.
Method 2. Advanced filter
If you have Excel 2003 or older, then you can use Advanced filter (Advanced Filter) from the menu (tabs) Data (Date).
Suppose we have the following list of randomly repeated company names:
Choose from the menu Data – Filter – Advanced filter(Data — Filter — Advanced Filter). We get a window:
In him:
- We highlight our list of companies in original range (List Range).
- Put the switch in position Copy the result to another location (Copy to another location) and select an empty cell.
- Enable (most importantly!) checkbox Only unique records(Uniqe records only) and click OK.
Get a list without duplicates:
If you need to search for duplicates not in one, but in several columns, then you can first glue them into one by making a kind of composite key using the function STsEPIT (CONCATENATE):
Then the further task will be reduced to finding duplicates already in one column.
Method 3. Selecting unique records with a formula
A slightly more complicated method than the first two, but it is dynamic, i.e. with automatic recalculation, i.e. if the list is edited or more elements are added to it, then they are automatically checked for uniqueness and selected. In the previous methods, if you change the original list, you will need to restart Advanced filter or press the button Removing duplicates.
So, again we have a list of randomly repeating elements. For example, like this:
The first task is to number all the unique representatives of the list, giving each one its own number (column A in the figure). To do this, paste in cell A2 and then copy the following formula down to the stop:
=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»»)
In the English version it will be:
=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;»»)
This formula checks how many times the current name has already appeared in the list (counting from the beginning), and if this number = 1, i.e. the element met for the first time – gives it a sequentially increasing number.
To simplify addressing, let’s give our ranges (for example, based on the fact that there can be up to 100 elements in the list) names. This can be done in new versions of Excel on the tab Formulas – Name Manager (Formulas — Name manager) or in older versions – through the menu Insert – Name – Assign (Insert — Name — Define):
- number range (A1:A100) – name NameCount
- to the entire list with numbers (A1:B100) – name NameList
Now it remains to choose from the list NameList all elements with a number will be our unique representatives. You can do this in any empty cell of adjacent columns by entering the following formula there with the well-known VLOOKUP function and copying it down to the entire column:
=ЕСЛИ(МАКС(NameCount)<СТРОКА(1:1);"";ВПР(СТРОКА(1:1);NameList;2))
or in the English version of Excel:
=IF(MAX(NameCount)
This formula goes from top to bottom in a column NameCount and displays all positions of the list with numbers in a separate table:
- Highlight duplicates by one or more columns in a list with color
- Prohibition of entering duplicate values
- Retrieving Unique Values with the PLEX Add-in