ExcelInsider Team
While recording data, it is not unusual to insert duplicate information by mistake. Sometimes, due to slow servers or due to recording errors, your excel sheet can end up with multiple transactions that are identical. If that’s the issue you are facing, do not worry. That is because in this article, we will show you three ways on how to remove rows containing identical transactions in excel.
➤ Select all the data from which you want to remove duplicate rows.
➤ From the Data tab, go to Data Tools, and select Remove Duplicates.
➤ Deselect the column that does not contain transaction data, and press OK.
That was a short guide, but there are some things that should be taken care of before applying this method. In this article, we will learn in detail how to remove duplicate rows using different methods. Therefore, stick around for a few moments to learn them properly.
Using Excel’s Built-In Method
In this sample dataset, we have some transaction information with IDs. It is clearly visible that there are two transactions that were recorded twice by mistake. Before processing the data further, these identical transactions must be removed. We will follow the methods below to do it:
Duplicate data is not uncommon in the field of data analysis. That’s why excel already includes a method to deduplicate them. This is how you follow that method:
➤ Select the table full of data with the transaction history.
➤ Go to the Data tab, and head to Data Tools > Remove Duplicates.
➤ Deselect the column that does not contain duplicate data. For example, in this case, the Transaction ID is unique, so we will deselect that column.
➤ Press OK.
Note:
Even if you do not select the whole Transaction ID column before removing duplicates, the process will work. However, the removed rows will be replaced by valid rows, so the transaction IDs of the valid rows will be replaced as well. That might cause an issue for most accountants to track the transactions.
Using IF-COUNTIF Functions
Instead of using the default method, we are now going to use two functions to find the duplicates and then delete them using a filter. Here is how to do it:
➤ Take a helper column. In this case, we are naming that ‘Duplicate identifier’. In the first cell (other than the header), write this formula:
=IF(COUNTIF($D$2:$D2, $D2)>1, “Duplicate”, “”)
➤ Autofill the function to other rows. Now you can identify the rows containing identical transactions.
➤ Select the newly created column header.
➤ From the Home tab, go to Sort & Filter > Filter.
➤ In the form heading, you will see a small arrow pointing down. Click on it. From the small dialog, select Duplicate and deselect everything else. Press OK.
➤ Now the spreadsheet will only show the duplicate rows containing identical transactions. Select them, right click, and hit Delete Row. If a warning shows up, press OK.
Note:
If you do not want to delete the rows and mess the table up, you can click Clear Contents to have blank rows instead.
➤ Go to Sort & Filter again and select Clear. From the same menu, click Filter again to remove the filter marker.
Copying Unique Data
The methods we mentioned before removed the duplicate transactions only. But you might want to copy the unique transactions to other columns. In that case, you need to use the procedure below:
➤ Select the columns containing duplicate data.
➤ Go to Data > Sort & Filter > Advanced.
➤ Select Copy to another location. From the Copy To section, select a set of blank columns that would fit the data you are trying to copy.
➤ Check the ‘Unique records only’ box.
➤ Hit OK.
Frequently Asked Questions
How do I delete alternate rows in Excel?
Create a helper column with two cells with 0 and 1. Then autofill the other cell. Finally, create a filter and delete either 0 or 1 filtered cells according to your choice.
How do I delete thousands of unused rows in Excel?
Create a filter that shows only blank rows. Then delete those rows and remove the filter.
How to replace occurrences in Excel?
From the Home tab, go to Find & Select > Replace. Then insert what you want to find, and what you want to replace it with. You can either replace a single instance or every one of them from there.
How do I delete non-consecutive rows in Excel?
While pressing Ctrl, click on the rows you want to delete. Then right-click on your mouse and select Delete Row.
How to use Excel to check for duplicates?
From the Home tab, go to Conditional Formatting > Highlight Cells Rules > Duplicate Values. Then, from the new dialog box, press OK. The duplicate cells will be shown in red boxes now.
Wrapping Up
In this article, we have learned how to remove rows containing identical transactions in excel. We hope that you can sort your data better by applying the techniques you found in this article. Do not forget to use the practice file for your convenience. Leave a comment below if you have some better ideas to share with us.
