1. Reverse Remove Duplicates In Excel
2. Reverse Remove Duplicates In Excel Rows
3. How To Remove Duplicate Lines In Excel

This example teaches you how to remove duplicates in Excel. Click any single cell inside the data set. On the Data tab, in the Data Tools group, click Remove Duplicates. The following dialog box appears. Leave all check boxes checked and click OK. Excel removes all identical rows (blue) except for the first identical row found. Similarly to flipping columns, you can use our Ultimate Suite for Excel to reverse the order data in rows. Just select a range of cells you want to flip, go to the Ablebits Data tab Transform group, and click Flip Horizontal Flip. In the Horizontal Flip dialog window, choose the options appropriate for your data set. I want to remove whole rows, not only duplicate cells in columns. I have searched the forum but cant find the exact answer to this. If the data looks like: AA AB BA CA AC I want to keep: AA AB CA So far I have only seen code to remove one-way duplicates (e.g. Delete all duplicate rows Excel vba), which would not remove any rows from the example. In Excel, we can use the Remove Duplicates feature to remove the same values from a list, but is there any trick to auto remove the duplicates from the list? In this article, I introduce the tricks on automatically removing duplicates if there is duplicate appearing in a specify Excel column. Remove Duplicates in Excel. Remove Duplicates in excel is used for removing the duplicate cells of one or multiple columns. This is very easy to implement. To remove duplicates from any column first select the column/s from where we need to remove duplicate values, then from the Data menu tab, select Remove Duplicate under data tools.

Removing duplicates is a very important part of any kind of data analysis. And your duplicate occurring can be quite different from others.

In this article, I will discuss how to use Excel to remove duplicate rows based on two columns. I will show several methods so that you can choose best one for you.

Let’s start…

Table of Contents

1) Remove duplicate rows based on two columns (Using ‘Remove Duplicates’ feature)

Check out the following data. Two columns are holding some names and their locations.

What I want is: only unique rows will be on the list; all the duplicate rows will be deleted/removed from the list.

Follow these steps.

1) Select a cell in the range. Open the Data tab. And click on the Remove Duplicates command in the Data Tools ribbon.

2) Remove Duplicates dialog box appears. Our data has headers, so ‘My data has headers’ is correctly checked mark. We want to remove duplicates based on both columns, so all the columns are checked mark, it is also alright. Click on the OK button.

This is the result we get:

7 duplicate values found and removed; 6 unique values remain. Cool, right?

Now, what if you want to keep your old data and get the results (data without duplicate rows) in a new place?

There is also a way. Use Advanced Filter feature of Excel. See this method in the next section.

2) Using Advanced Filter feature to remove duplicate rows (in a new location)

We shall apply the same data again for Advanced Filter feature.

1) Select a cell in the range => Data tab => Sort & Filter ribbon => click on the Advanced command button.

2) ‘Advanced Filter’ dialog box appears. You’re seeing that our data range $A$1: $B$14 is already input in the ‘List range’ field.

We want to show only the Unique values in a new location, so I select this check mark ‘Unique records only’ and I choose ‘Copy to another location’ radio button.

In the ‘Copy to’ field, I input a cell reference ($D$1) from the same worksheet (you can also input a cell from another worksheet).

Everything is done. I just click OK. You see, 6 unique rows are placed in cell reference D1.

3) Removing duplicates based on some specific columns of a range

This is a little bit different.

You have some columns and you want that finding and removing duplicates will happen based on some specific columns, not based on every column in the range.

In the following image, you’re seeing some dummy data. And I want to remove duplicate rows based on column A, B, and C. Column D will not affect this filtering.

For example, row 5, row 9 and row 13 are duplicates though their Sales column has different values ($5200, $6000, $7000).

Follow these steps to remove these types of duplicates.

1) Select a cell in the range => Data tab => Data Tools ribbon => click on the Remove Duplicates command button.

Reverse Remove Duplicates In Excel

2) ‘Remove Duplicates’ dialog box appears. All the columns are by default selected. But we want to exclude our ‘Sales’ column from this criterion. So, I just unselect the check mark on the left side of the ‘Sales’.

3) Setup is done. I just click OK. And this is the result we get. 4 duplicate values found and removed; 9 unique values remain in the range.

4) Removing duplicates using Excel formula

This is an interesting way if you like to do things with Excel formulas.

We shall use Excel COUNTIFS () function. Before working with the main formula, let’s take a look at COUNTIFS () function’s syntax and uses.

COUNTIFS () function returns the total number of occurring from multiple ranges based on multiple criteria. If you want to know more about this function, check out this page.

Let’s now remove duplicates using the formula.

Going to apply the formula on the same data set as we used in our first example.

Under column C, we add a new header as ‘Find Duplicates’ and use the following formula in cell C2:

=IF(COUNTIFS($A$2:A2, A2, $B$2:B2, B2)>1, 'I'm duplicate. Delete me', 'Original')

I press Enter and drag down the formula for all the cells in the column.

So, we get only 6 original values and 7 duplicate values.

Now you can delete these duplicate values manually or converting this range into an Excel table.

I am leaving the manual thing on you.

Here is the table making way.

Just select a cell in the range and convert the range into a table.

Filter this table for showing only the duplicate values.

Now delete these rows.

Get back your Original values.

Here is the result.

6 original values.

Download Working File

So, these are my ways of deleting duplicate rows based on two columns. I have excluded the VBA part intentionally (to make it easy for all).

If you know any other better technique to remove duplicate rows from two columns, please let me know in the comment box.

Reverse Remove Duplicates In Excel Rows

Happy excelling 🙂

Related Readings

How To Remove Duplicate Lines In Excel