In many cases, we record transactions in Excel. And in various ways, we may include the same data in multiple rows which causes problems when we work with the data. Here, I will show 4 easy methods to remove rows containing identical transactions in Excel.
How to Remove Rows Containing Identical Transactions in Excel: 4 Easy Methods
We can remove rows containing identical transactions in Excel following different methods. Here, I will show 4 easy methods to remove rows containing identical transactions in Excel. For demonstration, I have introduced a dataset having Date, Code, Sold, On Hand, Purchase Price, and Balance Value for some transitions of a company.
1. Erase Rows Containing Identical Transactions Using Remove Duplicates Option
The easiest way to remove rows containing identical transactions can be done with a few clicks on the ribbon. Let’s see the stepwise procedures.
- First, select the dataset.
- Next, go to the Data tab and click on Remove Duplicate Rows.
- In the Remove Duplicates window, Select All and put a mark on My data has headers.
- Finally, Press OK.
- As a result, duplicate transactions will be removed.
2. Apply Advanced Filter Option to Remove Rows Containing Same Transactions
We can also use the Advanced Filter option of Excel to remove rows containing the same transactions. Let’s walk through the procedures.
- First, select the dataset.
- After that, go to the Data tab and select Advanced Filter.
- In the Advanced Filter window, put a mark on Copy to another location.
- Also, put a tick mark on Unique records only.
- As well as select a cell where the copy of the dataset without duplicates should appear.
- Finally, press OK.
- Simultaneously, we will see a new dataset without duplicate transactions.
3. Insert IF and COUNTIFS Functions to Remove Rows Containing Identical Transactions
We can do the same task by using the If and COUNTIFS functions of Excel. Let’s follow the stepwise procedures given below.
- First, we have to take an additional column Status, and write the following formula in Cell H5.
=IF(COUNTIFS($B$5:$B5,$B5,$C$5:$C5,$C5,$D$5:$D5,$D5,$E$5:$E5,$E5,$F$5:$F5,$F5,$G$5:$G5,$G5)>1,"Duplicate row","Unique")- Next, press Enter.
- Further, use the Fill Handle to copy the formula to the following cells.
- Consecutively, we will see the Status as Unique or Duplicate Rows.
In the formula,
- The COUNTIFS function checks a cell value with the values through the respective column and count. If the count number is greater than 1 then the IF function gives the output Duplicate Rows otherwise gives the output Unique.
- Now, we will filter the Status column to remove the duplicates.
- So, go to the Data tab and select Filter.
- Further, click on the small filter icon beside Status and select Sort Z to A.
- The duplicate rows will be listed below, select them and delete them using the context menu.
- Also, In the Delete window, select Entire row.
- Finally, press OK.
- We can see the rows with identical transactions are no more present.
4. Apply Excel VBA to Remove Rows with Identical Transactions Keeping the First Instance
We can apply Visual Basics for Applications (VBA) code to remove the rows with identical transactions.
Let’s follow the steps given below.
- In the first step, open the VBA window by pressing Alt + F11.
- After that, from VBA Projects select active worksheet.
- Now, right–click and select Insert > Module.
- Next, write the following code in the Module window:
Sub RemoveIdenticalRows()
Dim Rg As Range
Set Rg = Range("B4:G12")
Rg.RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes
End Sub
- Then, press Run on the VBA.
- Finally, come back to the worksheet and see the identical transaction rows are no more present.
How to Find Duplicates Using Formula in Excel
We can detect the presence of duplicates quite easily by using the IF & COUNTIFS functions of Excel. I am showing the stepwise procedures below.
- First, we have to take an additional column Status, and write the following formula in Cell H5.
=IF(COUNTIFS($B$5:$B5,$B5,$C$5:$C5,$C5,$D$5:$D5,$D5,$E$5:$E5,$E5,$F$5:$F5,$F5,$G$5:$G5,$G5)>1,"Duplicate row","Unique")- Next, press Enter.
- Further, use the Fill Handle to copy the formula to the following cells.
- Consecutively, we will see the Status as Unique or Duplicate Rows.
In the formula,
- The COUNTIFS function checks a cell value with the values through the respective column and count. If the count number is greater than 1 then the IF function gives the output Duplicate Rows otherwise gives the output Unique.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Having identical transaction records in the dataset causes various problems. Here, I have shown 4 easy methods to remove rows containing identical transactions in Excel. Please leave a comment if you have any suggestions.
Related Articles
- How to Delete Multiple Rows in Excel Using Formula?
- How to Delete Hidden Rows in Excel?
- How to Delete Filtered Rows in Excel?
- How to Delete Unused Rows in Excel?
- How to Find and Delete Rows in Excel
- How to Delete Multiple Rows in Excel with Condition?
- How to Delete Rows in Excel without Affecting Formulas?
- How to Delete Rows in Excel That Go on Forever?
- How to Delete Infinite Rows in Excel?
