Reverse Remove Duplicates In Excel

• How To Remove Duplicate Names In Excel
• How Do I Remove Duplicates In Excel
• Undo Remove Duplicates In Excel

How to remove duplicates but keep rest of the row values in Excel?

How To Remove Duplicate Names In Excel

How to Unmerge Cells in Excel. This wikiHow teaches you how to change a merged cell back into two or more standard cells in Microsoft Excel. Open your Excel document. Double-click the Excel document that you want to open. However, the building is the 3rd to last column from the end - so if we reverse it, the data will be in column 3 for both rows. Let's put our data in a spreadsheet in Excel. In Excel, make sure you have the Developer tab enabled. If not, go to File - Options - Customize Ribbon and check the box next to Developer in the right pane. If you don't want to do a formula.use Conditional Formatting Highlight Cell Rules More Rules. Unique or Duplicate Values. Choose unique from the drop down and pick a color for the format box. Then you can add filters to your sheet, filter by color, and then delete the rows that are highlighted. Excel Memory Stack. Excel uses a portion of the computer's RAM to maintain a list (often called a stack) of recent changes made to a worksheet. The undo/redo combination of commands allows you to move forward and backward through the stack to remove or re-apply those changes in the order they were first made. This page describes how to remove duplicate rows in Excel, using three different methods. If you want to remove duplicate cells (rather than entire rows of data), you may find the Remove Duplicate Cells page more straightforward. In order to illustrate how to remove duplicate rows in an Excel spreadsheet, we will use the example spreadsheet below, which has data spanning three columns.

In Excel, it will remove all duplicate values and move up when you apply the Remove Duplicates function as screenshot 1 shown. However, in some cases, you may want to remove duplicates but keep the rest of the row values as screenshot 2 shown. Now, in this case, I will introduce some tricks on deleting duplicates but keeping the rest in Excel.

Remove duplicates but keep rest of row values with Kutools for Excel (2 steps)

Remove duplicates but keep rest of row values with Filter

With a formula and the Filter function, you can quickly remove duplicates but keep rest.

1. Select a blank cell next to the data range, D2 for instance, type formula =A3=A2, drag auto fill handle down to the cells you need. See screenshot:

2. Select all data range including the formula cell, and click Data > Filter to enable Filter function. See screenshot:

3. Click at the Filter icon in Column D (the formula column), and check TURE from the drop down list, see screenshot:
Hp un2430 mobile broadband module windows 10.

4. Click OK, and then all duplicates have been list, and select all of the duplicate values, press Delete key to remove them. See screenshot:

5. Click Data > Filter to disable Filter, and remove the formulas as you need. You can see all duplicates have been removed and the rest of values are kept in the row.

Remove duplicates but keep rest of row values with VBA

In Excel, there is a VBA code that also can remove duplicates but keep rest of row values.

1. Press Alt + F11 keys to display Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste below code to the Module.

VBA: Remove duplicates but keep rest of row values

3. Press F5 key to run the code, a dialog pops out to remind you to select a range to remove duplicate values from. See screenshot:

4. Click OK, now the duplicate values have been removed from selection and leave blank cells.

Remove duplicates but keep rest of row values with Kutools for Excel

If you have Kutools for Excel – a handy and powerful add in tool installed, you can quickly remove duplicates but keep rest or row values through two wayss.

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

Method 1 Merge Same Cells (2 steps)

1. Select the duplicate values, click Kutools > Merge & Split > Merge Same Cells. See screenshot:

2. Then the duplicate values have been merged into one cell. And click Home > Merge & Center > Unmerge Cells to split them. See screenshot:

Now the result has been shown as this:

Method 2 Select Duplicate & Unique Cells (4 steps)

1. Select the list of data you want to remove duplicates from, and click Kutools > Select > Select Duplicate & Unique Cells. See screenshot:

2. In the Select Duplicate & Unique Cells dialog, check Duplicates (Except 1st one) option in the Rule section. See screenshot:

3. Click Ok, a dialog pops out to remind you how many duplicates have been selected, click OK to close it. See screenshot:

4. Then press Delete key to remove the selected duplicate values.

Remove Duplicates But Keep Rest of Row

Tip: with Kutools for Excel’s Advanced Combine Rows utility, you can combine the duplicate values and then do some calculations on another column as below screenshot shown. It’s full function without limitation in 30 days,please download and have a free trial now.

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails..
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range..
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns.. Prevent Duplicate Cells; Compare Ranges..
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select..
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more..
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments..
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic..
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF..
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

• To post as a guest, your comment is unpublished.
  i would use the formula =A3<>A2 (not equal to ) so that it show up on top vs. last one on bottom.
• To post as a guest, your comment is unpublished.
  the true false really helped!
• To post as a guest, your comment is unpublished.
  The True/False solution is a smart trick. Thanks
• To post as a guest, your comment is unpublished.
  Is it possible to merge the like cells without the Kutools add-in?

  • To post as a guest, your comment is unpublished.
    Heloo, MO, this article may help you. https://www.extendoffice.com/documents/excel/4864-excel-combine-rows-with-same-id-name.html
• To post as a guest, your comment is unpublished.
  In the first method, instead of =A3=A2, you can do =A2=A1 to keep the first instance and delete the rest of the duplicates.
• To post as a guest, your comment is unpublished.
  Thank you for this tutorial, it saves me from tons of work removing duplicates.

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?

How Do I Remove Duplicates In Excel

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.

Undo Remove Duplicates In Excel

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