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