How to Remove Rows Containing Identical Transactions in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

how to remove rows containing identical transactions in excel


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.

Erase Rows Containing Identical Transactions Using Remove Duplicates Option

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

Apply Advanced Filter Option to Remove Rows Containing Same Transactions

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

how to remove rows containing identical transactions in excel result

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

Insert IF and COUNTIFS Functions to Remove Rows Containing Identical Transactions

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

how to remove rows containing identical transactions in excel result

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


Similar Readings


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.

Apply Excel VBA to Remove Rows with Identical Transactions Keeping First Instance

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, rightclick 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

Note: In the code, B4:G12 is the range of the dataset. Change it according to your one.
  • Then, press Run on the VBA.

  • Finally, come back to the worksheet and see the identical transaction rows are no more present.

how to remove rows containing identical transactions in excel result

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

How to Find Duplicates Using Formula in Excel

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.

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

Mehedi Hasan Shimul

Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo