How to Remove Rows Containing Identical Transactions in Excel

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.

Download Practice Workbook

You can download the practice workbook from here.

4 Easy Methods to Remove Rows Containing Identical Transactions in Excel

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.

Read More: How to Delete Duplicate Rows in Excel with VBA (8 Effective Ways)

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.

Read More: How to Filter Data and Delete Rows with Excel VBA (5 Examples)

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.

• 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.

• 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 delete them using 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.

Read More: How to Delete Rows Based on Another List in Excel (5 Methods)

Similar Readings

• How to Remove Highlighted Rows in Excel (2 Easy Ways)
• [Fixed!] Not Enough Memory to Delete Rows Error in Excel
• How to Delete All Rows Not Containing Certain Text in Excel
• Excel VBA to Delete Rows with Specific Data (9 Examples)
• How to Filter and Delete Rows with VBA in Excel (2 Methods)

4. Apply Excel VBA to Remove Rows with Identical Transactions Keeping 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 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.

Read More: How to Remove Duplicate Rows Based on One Column Using Excel VBA

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.

• 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.

Read More: Excel VBA Code to Delete Rows Based on Multiple Cell Value (3 Criteria)

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. Visit our ExcelDemy Website for similar articles regarding Excel.

Related Articles

• How to Delete Selected Rows with Excel VBA (A Step-by-Step Guideline)
• Excel VBA: Delete Row If Cell Is Blank (A Complete Guide)
• How to Delete Row Using VBA (14 Ways)
• Excel Delete Rows in a Range with VBA (3 Easy Ways)
• Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)
• How to Delete Multiple Rows in Excel (3 Methods)