SOLVED

Clear repeating values in column A, keep rows intact

Copper Contributor

abc
abc
abc
bcd
bcd
cde
cde
cde
To appear as
abc

 

 

bcd

 

cde

 

 

 

6 Replies
best response confirmed by Ranju0714 (Copper Contributor)
Solution

@Ranju0714 

Option 1: using formulas.

Let's say the data are in A1 and below.

Enter =A1 in B1.

Enter =IF(A2=A1,"",A2) in B2.

Fill down from B2.

If you wish, you can copy column B and paste it as values over column A. You can then remove the formulas in column B.

 

Option 2: using a macro.

Sub ClearDuplicates()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = m To 2 Step -1
        If Range("A" & r).Value = Range("A" & r - 1).Value Then
            Range("A" & r).ClearContents
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

@Ranju0714 

 

'VBA code
Sub ClearWithoutShiftRows()
    rws = Cells(Rows.Count, "A").End(xlUp).Row
    For j = rws To 2 Step -1
        If Cells(j, 1).Value = Cells(j - 1, 1).Value Then Cells(j, 1).Value = ""
    Next
End Sub
Thanks much, formula worked
Not know VBA - where does this code go

@Ranju0714 

ALT+F11 keys combination

JulianoPetrukio_0-1632405880272.png

 

Then paste the code into the module

After you run pressing F5

@Ranju0714 I see you have an answer, here is a second way to do it.

 

=IF(SEQUENCE(COUNTA(B3:B10))=XMATCH(B3:B10;B3:B10);INDEX(B3:B10;SEQUENCE(COUNTA(B3:B10)));"")

 

Example attached. I am starting to love sequence. 

 

- Geir

1 best response

Accepted Solutions
best response confirmed by Ranju0714 (Copper Contributor)
Solution

@Ranju0714 

Option 1: using formulas.

Let's say the data are in A1 and below.

Enter =A1 in B1.

Enter =IF(A2=A1,"",A2) in B2.

Fill down from B2.

If you wish, you can copy column B and paste it as values over column A. You can then remove the formulas in column B.

 

Option 2: using a macro.

Sub ClearDuplicates()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = m To 2 Step -1
        If Range("A" & r).Value = Range("A" & r - 1).Value Then
            Range("A" & r).ClearContents
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

View solution in original post