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.