Excel delete duplicated data in consecutive rows

This Excel tutorial explains how to delete duplicated data in consecutive rows (delete the value, not delete row).

You may also want to read:

Excel delete blank rows

Remove duplicates in text

Excel delete duplicated data in consecutive rows

In conventional Pivot Table layout (Tabular), all data are grouped together and duplicated data would not repeat.

Division Department
Div1 Dept1
Div2 Dept2
Dept3
Dept4
Div3 Dept5
Dept6

 

In database, data are stored in each row with repeating value, therefore you would download the data from most systems as below.

Division Department
Div1 Dept1
Div2 Dept2
Div2 Dept3
Div2 Dept4
Div3 Dept5
Div3 Dept6

In my old post, I wrote a Macro to convert the Pivot Table layout to database layout, in this post I will demonstrate how to convert the database layout to Pivot Table layout.

Excel delete duplicated data in consecutive rows – non VBA

The easiest way to convert from database layout to Pivot Table layout is to create a Pivot Table. Unfortunately, the order of the data will be sorted. If you want to maintain the same order, follow the below example.

Suppose you want to remove duplicate in column A, create an assist column in column C. In C3, type the formula as below, Autofill the formula down the row.

 

Because C2 (the first data) 7does not need formula, simply copy A2 to C2.

Excel delete duplicated data – VBA

If you don’t like creating an assist column, or you have more than one columns to convert, you can consider using VBA.

Press ALT+F11, copy and paste the below code in a new Module.

Public Sub removeDuplicate()
    Dim delCellArr()
    'Define the data range
    Set dataRng = ActiveSheet.Range("B2:C8")
    
    For Each Rng In dataRng
        If Rng.Value = Rng.Offset(-1, 0).Value Then
            n = n + 1
            ReDim Preserve delCellArr(n)
            delCellArr(n) = Rng.Address
        End If
    Next
        
    For i = 1 To UBound(delCellArr())
        Range(delCellArr(i)).Value = ""
    Next i
End Sub

Excel delete duplicated data – Example

Suppose we have the below data with repeating Division and Department.

Define the delete Range in the VBA code as B2:C8, then run the Macro. Now all the duplicated values in column B and column C are removed.

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *