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…

1. Remove Duplicates Using the Excel Advanced Filter. The Excel advanced filter has an option that allows you to filter unique records (rows of data) in a spreadsheet and copy the resulting filtered list to a new location.
2. Instead of removing duplicates, I would like a list of the duplicates Excel would remove. The data is imported from another program, and I would like to clean up the source data, so that over time, there would be no duplicates.
3. Excel's built-in Remove Duplicates tool can help you get find and remove those duplicate records in one swoop. Click on any cell in your spreadsheet that contains data.

I frequently use the remove duplicate function to do a quick count of unique numbers, and to make sure all expected numbers are present. I then undo the remove duplicates to put the data back where was. This has always worked in the past, but today after I hit undo, my rows are mixed. I have tried restarting everything with no luck. Hi Everyone, i have data like below. I want to remove duplicate row 'Data level & District & Cust ID'. Data Level District Sales Value Customer id District level Bagpat 1037764.267 0 District level Bagpat 1037764.267 0 District level Bagpat 1037764.267 0 District level Bijnor 1037764.26.

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.

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.

Happy excelling 🙂

Related Readings

Excel sheets can get really huge. They could extend to hundreds and thousands of data rows. And when that happens it becomes really difficult to manage the document. The greatest problem that I frequently come across in such cases is repetition and duplication of data.

Trust me, finding and getting rid of the duplicates is a challenging task. It takes a lot of time and effort. Of course there’s a Remove Duplicates option that Excel provides but that only facilitates bulk removal of duplicates from specific columns or rows and doesn’t let you have a lot of control on how the data should be removed.

If you start sorting it manually, you can spend your day scrolling up and down the sheet without actually achieving anything. Some people take up to using the find feature. I would not consider that a bad idea. But there are better ways to get the task done than involving yourself in this drudgery, right? We will discuss one such trick today.

If you have heard of or used Conditional Formatting on Excel you might have guessed what I am talking about. If not, you should read on and adopt the trick.

Cool Tip: Another cool data management and formatting feature comes in the form of transpose option. With that you can easily switch rows into columns and columns into rows on Excel sheets.

Steps to Find Duplicate Data on Excel

First and foremost, get the sheet that you wish to scan, up and ready. Then follow the steps below:

Step 1: Select the cells that contain the data to be scanned. It may extend for one column/row to the entire sheet. Here’s the sample data that I have taken for this example.

On the sheet I have selected the first column for examination.

Step 2: Once the data is selected, navigate to Home tab. Under Styles, expand Conditional Formatting and then go to Highlight Cell Rules -> Duplicate Values.

How To Remove Duplicate Names In Excel

Step 3: Upon selecting Duplicate Values you will be presented with a dialog box. Keeping the selection as Duplicate choose your formatting option. I kept it to Light Red Fill with Dark Red Text.

Reverse Remove Duplicates In Excel Rows

Step 4: Click on Ok to go back to the sheet. There, you will see the duplicated data highlighted in the format you have chosen. Check out the example that I have considered.

Again, check the same examination for the second column (alone) and both the columns (together). Is it not interesting?

Conditional formatting has got many more choices and rules that you can apply for various other things like data validation, comparisons, calculations and more. In case you fall short of a requirement, you can create your own rule.

Conclusion

Once you give this process a try you will realize how much time and effort you can save for yourself. There’s no doubt that it is better than the manual process. If you do not agree, you must be using some other and a better trick to get across this problem. If that is the case do share your trick with us. We would be more than glad to learn.

The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.Also See#msexcel #How-to/Guides

Can I Undo Remove Duplicates In Excel

Did You Know

How Do I Remove Duplicates In Excel

In 2017, Apple outsold Rolex as the world’s top watchmaker in terms of revenue.

More in Windows

Top 4 Tools to Extract RAR Files in Windows 10