Sometimes it is necessary to remove duplicate items from a column or duplicate rows out of an entire table. Removing duplicates will help you create unique item lists and allow you to gain better insights into your data.
Contents:
Removing Duplicates
In these examples, we will be going over different scenarios in which you may find yourself wanting to remove duplicates. From rows, columns, tables, or even a dynamic array, there are many different tools and methods to help you remove any unnecessary data from your workbooks.
How to Remove Duplicates from a Column
Removing duplicate items from a single column is easy. In this example, we'll start with a list of customers that have made multiple purchases in the past month, and condense this into a list of customers with no duplicates.
The first step is to highlight the column or range that you wish to remove duplicate items out of.
From there, using the top ribbon, navigate to the Data tab > Data Tools> and Remove Duplicates.
This will open the "Remove Duplicates" menu, which will give you more control over how you wish to delete the duplicate items.
The shortcut Alt + A + M can also be used to pull up the "Remove Duplicates" menu and is much faster than clicking through the ribbon menu.
Once the Remove Duplicates menu has been opened, you'll see a list of columns that have been selected, as well as options to select or unselect the below columns.
If your selected data includes headers, be sure to check the box in the upper right, as this will exclude those values from being removed.
Once you hit Ok, You'll then be greeted by a message that looks like this, notifying you that out of your selected range, duplicate values were found and removed, and also the count of remaining unique values.
How to Remove Duplicates from a Row
In this example, let's say we have a row of values that we need to strip out the duplicates. In this case, dates when a product was sold.
The "Remove Duplicates" feature won't work with our data in a row, so we need to convert it into a column. To do this, we need to select the entire row, copy it, and transpose it. For this we can use Paste Special.
By either using the Paste Special menu option on the Home Ribbon as seen on the left, or using the shortcut
Alt + E + S + E , click the transpose option, and hit enter, and your data will now be transposed into one column, reading from up to down, rather than left to right.
From here, simply follow the steps outlined in the previous example (use the "remove duplicates" feature in the data tab), and you'll be left with a list of unique items.
To turn this column of data back into a row, you can reverse the process by selecting the range of cells and transposing them once more.
How to Remove Duplicates Across Multiple Columns
Removing duplicates across more than one column follows the same process as removing duplicates out of a single column.
It is important to note that when removing duplicates across multiple columns, a row will be considered a duplicate if the same values in each column appear multiple times. This means the entire row/all columns must be unique.
For example, in the table to the left, no duplicates would be found, as each row contains unique column values.
Even though Jackson Sales Center shows up multiple times, the Product SKU never repeats, meaning that each row in this table is unique, and that no duplicates exist.
The first step to remove duplicates from multiple columns is to select the entire range, all columns and rows, that you wish to remove the duplicate from.
Then, pull up the remove duplicates menu, either by using the top ribbon, and navigating to the Data tab > Data Tools> and Remove Duplicates, or the shortcut
Alt + A + M .
Hit "Ok", and that's it! All duplicate rows across your selected columns will be removed, leaving only the unique rows behind.
How to Remove Duplicates Using a Formula
If you do not want to actually delete out data from the range you want to remove duplicates from, or whether you want to use the unique range of data in formulas in another area of your workbook, the UNIQUE function may be what you're looking for.
To use the UNIQUE function, all you have to do is type =UNIQUE( and highlight the range that you wish to strip out the duplicates from. This will leave you with a list of unique values (unformatted however), that you can use anywhere else in your workbook.
This also works the same with multiple columns selected and will return unique rows as a dynamic array.
How to Remove Duplicates from a Dynamic Array
Whenever you're working with dynamic arrays, you may run into a scenario in which you want to take a dynamic array, and pull out all of the duplicate values.
To do this is very simple as dynamic arrays were built to work seamlessly with every dynamic formula, such as FILTER, SEQUENCE, SORT, and XLOOKUP.
In this example, let's look at a dynamic array that is filtering our sales data for all sales by "Designs and Things".
= FILTER(SalesData[[Order '#]:[Sales Person]], SalesData[Customer Name] = "Designs and Things")
To remove all the duplicates from this dynamic array, we can simply add the UNIQUE function to the beginning of this formula, which will filter out of the duplicate row, leaving us with only unique values in our array.
= UNIQUE(FILTER(SalesData[[Order '#]:[Sales Person]], SalesData[Customer Name] = "Designs and Things"))
The UNIQUE function can be used in this way alongside any dynamic array that you may be working with, and will strip out the duplicate values. It can be especially useful when creating a dashboard or reporting where you may need lists of unique values to dynamically update whenever new information is available.