Remove duplicate rows based on two columns (Using ‘Remove Duplicates’ feature) Check out the. Microsoft kindly added a remove duplicate function into Excel for just this occasion. Open your spreadsheet on the page you need to sort. Press Ctrl + A to select all. Click the Data tab and select Remove Duplicates. Select or deselect ‘My data has headers’ depending on whether yours has them or not. Click OK to remove the duplicates.
How to remove duplicates but keep rest of the row values in Excel?
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:
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.
with more than 300 handy functions, makes your jobs more easier. |
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.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- 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.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- 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!
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
- 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.
Reversing a string may not have many real-world applications, especially when working with spreadsheets. That is why you will not find any built-in function for this in Excel.
But there might be cases where you need to reverse a string, for whatever reason. Maybe you need to generate a special code, maybe you need to see if a string is a palindrome, or maybe you just want to do it for fun.
Even though there aren’t any built-in Excel functions for string reversal, there are a number of different ways to do this. You can use either a formula or a script written in VBA.
In this tutorial, we will see two ways to reverse a text string using a formula. If you like to use macros, then we also have a VBA script to help you reverse multiple strings quickly.
Reversing Text String using Text Formulas
Let us first understand the main process involved in reversing a string.
Let’s say you have a single cell with text that you want to reverse.
To reverse the string “Hello” in cell A1, follow these steps:
- In cell B1, type the following formula: =MID($A$1,LEN($A$1)-ROW(B1)+1,1). This should return the last character in the given string, which is “o”.
- Drag the fill handle down (situated at the bottom right of cell B1) until you start seeing “#VALUE!”.
- You should now see each character of the string in “Hello” in each cell of column B, but reversed. Clear any cells containing “#VALUE!”. Here’s what you should finally see in our example:
- After the last cell of column B, i.e. in cell B6, type the formula: =TRANSPOSE(
- Drag and select the cells B1 to B5.
- Close the parentheses for the TRANSPOSE formula.
- Now select the whole formula in the formula bar and press F9 on your keyboard. This should display the values of each cell from B1 to B5, separated by commas and the whole thing should be surrounded by curly brackets: {“o”,”l”,”l”,”e”,”H”}.
- Remove the opening and closing curly brackets.
- Type “CONCATENATE”, followed by opening parentheses after the equal to sign.
- Close the parentheses after “H”.
- Press the return key.
You should now see the reverse of the string “Hello” in cell B6.
How To Remove Duplicate Names In Excel
What happened here?
Now let us look at what the formula did.
The function LEN($A$1) returns the length of the string in cell A1. We don’t want this cell reference to change when copied to the cells below B1, so we locked the cell reference by adding ‘$’ signs to it. The length of the string “Hello” is 5, so this function will return the value, ‘5’.
The function ROW(B1) simply returns the row number of cell B1. We might as well have typed ‘1’ instead of ‘ROW(B1)’, but wanted this value to change each time it was copied to a new cell. So when it’s copied to cell B2, it changes to value ‘2’. When copied to cell B3, it changed to value ‘3’ and so on.
Now, LEN($A$1)-ROW(B1)+1 gives us the value ‘5’, which is the index of the last character in “Hello”.
The function MID() returns the characters at a given index. It takes three parameters – A string or reference to a string, an index, and the number of characters to return starting at that index.
Here, the formula =MID($A$1,LEN($A$1)-ROW(B1)+1,1) takes the reference to cell A1 as the first parameter, the index, 5 as the second parameter and the number 1, as the third parameter. So the formula returns the character at index 5 of cell A1. This means we get the value “o” – the last character of the string.
Here’s what’s happening when you break down the formula:
- =MID($A$1,LEN($A$1)-ROW(B1)+1,1)
- =MID($A$1, 5 – 1 + 1, 1)
- =MID($A$1, 5, 1)
- =”o”
What happens when the formula is copied to cell B2?
In cell B2, the formula now changes to:
Here’s what’s happening when you break down the formula:
- =MID($A$1,LEN($A$1)-ROW(B2)+1,1)
- =MID($A$1, 5 – 2 + 1, 1)
- =MID($A$1, 4, 1)
- =”l”
How Do I Remove Duplicates In Excel
What happens when the formula is copied to cell B5?
Reverse Remove Duplicates In Excel Shortcut
In cell B5, the formula now changes to:
Here’s what’s happening when you break down the formula:
- =MID($A$1,LEN($A$1)-ROW(B5)+1,1)
- =MID($A$1, 5 – 5 + 1, 1)
- =MID($A$1, 1, 1)
- =”H”
Reverse Remove Duplicates In Excel Spreadsheets
So the function returns the first character of the string in A1.
Reverse a Text String in Excel using TRANSPOSE Formula
Although the above method works fine, it is not very practical as you would need to involve a whole lot of cells in your sheet. We demonstrated the above method just to break down the technique and make it easy for you to understand what’s happening.
Here’s a shortened down and more practical version of the above technique:
- In cell B1, type the formula:
- Select the entire formula in the formula bar and press F9 on your keyboard.
- This should display each character of your string, reversed and separated by commas. The whole thing should be also surrounded by curly brackets, which means this is an array: {“o”,”l”,”l”,”e”,”H”}.
- Remove the opening and closing curly brackets.
- Type “CONCATENATE”, followed by opening parentheses after the equal to sign.
- Close the parentheses after “H”.
- Press the return key.
You should now see the reverse of the string “Hello” in cell B1. This calculation got done directly and in one cell. So it is a much more efficient way of using the formula to reverse a string.
There are a few things to note about this technique though:
- The reversed string will not change when you change the value in cell A1. You will need to repeat this process every time there’s a change.
- This method is alright if you have a few strings that you want to reverse, but if you have a whole list of strings to reverse, it might prove to be a little painstaking. This is because you need to always repeat steps 2 to 7 for every string.
Due to the above 2 disadvantages, we recommend using a VBA script to reverse text string in Excel.
Reversing Characters of a Text String in Excel using VBA Macro
Using VBA might sound a little intimidating if you’ve never used it before, but it can be an easier method to reverse strings in your worksheet.
Here’s the VBA code that we will be used to reverse a string in a single cell. Feel free to select and copy it.
Follow these steps:
Reverse Remove Duplicates In Excel Rows
- From the Developer Menu Ribbon, select Visual Basic.
- Once your VBA window opens, Click Insert->Module. Now you can start coding. Type or copy-paste the above lines of code into the module window. Your code is now ready to run.
- Select a single cell containing the text you want to reverse. Make sure the cell horizontally next to it is blank because this is where the macro will display the reversed string.
- Navigate to Developer->Macros->Reverse_String->Run.
You will now see the reversed string next to your selected cell.
Note: If you can’t see the Developer ribbon, from the File menu, go to Options. Select Customize Ribbon and check the Developer option from the Main Tabs.
Finally, Click OK.
Reversing a List of Strings in Excel using VBScript
Using VBScript to reverse a long list of strings is easy too! Say you want to reverse the following set of names:
Here’s the VBA code that we will be using to reverse all strings in a column. Select and copy it.
Follow these steps:
Function To Remove Duplicates In Excel
- From the Developer Menu Ribbon, select Visual Basic.
- Once your VBA window opens, Click Insert->Module. Now you can start coding. Type or copy-paste the above lines of code into the module window. Your code is now ready to run.
- Select the range of cells containing the text you want to reverse. Make sure the column next to it is blank because this is where the macro will display the reversed strings.
- Navigate to Developer->Macros->reverse_string_range->Run.
You will now see the reversed strings next to your selected range of cells.
Do remember to keep a backup of your sheet, because the results of VBA code are usually irreversible.
To wrap it up, we saw two ways to reverse strings in Excel. One method uses a formula and the other method uses VBA code.
The first method is alright if you don’t really feel comfortable working with VBA code. But it is beneficial only if you have one or a few cells that you want to reverse.
If there are more cells that need to be reversed, it can prove to be time-consuming.
Can I Undo Remove Duplicates In Excel
Instead, you can use the second method (using VBA code), because it saves time and with just a few clicks, you can get a whole list of strings reversed in one go.
Although there are no straight ways to reverse a text string in Excel, I hope the methods shown here will help you get it done easily.
I hope you found this tutorial useful.
Other Excel tutorials you may find useful: