SOLVED

Clear repeating values in column A, keep rows intact

%3CLINGO-SUB%20id%3D%22lingo-sub-2778203%22%20slang%3D%22en-US%22%3EClear%20repeating%20values%20in%20column%20A%2C%20keep%20rows%20intact%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778203%22%20slang%3D%22en-US%22%3E%3CP%3Eabc%3CBR%20%2F%3Eabc%3CBR%20%2F%3Eabc%3CBR%20%2F%3Ebcd%3CBR%20%2F%3Ebcd%3CBR%20%2F%3Ecde%3CBR%20%2F%3Ecde%3CBR%20%2F%3Ecde%3CBR%20%2F%3ETo%20appear%20as%3CBR%20%2F%3Eabc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebcd%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecde%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2778203%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2778231%22%20slang%3D%22en-US%22%3ERe%3A%20Clear%20repeating%20values%20in%20column%20A%2C%20keep%20rows%20intact%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778231%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164479%22%20target%3D%22_blank%22%3E%40Ranju0714%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOption%201%3A%20using%20formulas.%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20data%20are%20in%20A1%20and%20below.%3C%2FP%3E%0A%3CP%3EEnter%20%3DA1%20in%20B1.%3C%2FP%3E%0A%3CP%3EEnter%20%3DIF(A2%3DA1%2C%22%22%2CA2)%20in%20B2.%3C%2FP%3E%0A%3CP%3EFill%20down%20from%20B2.%3C%2FP%3E%0A%3CP%3EIf%20you%20wish%2C%20you%20can%20copy%20column%20B%20and%20paste%20it%20as%20values%20over%20column%20A.%20You%20can%20then%20remove%20the%20formulas%20in%20column%20B.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOption%202%3A%20using%20a%20macro.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20ClearDuplicates()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20m%20%3D%20Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20For%20r%20%3D%20m%20To%202%20Step%20-1%0A%20%20%20%20%20%20%20%20If%20Range(%22A%22%20%26amp%3B%20r).Value%20%3D%20Range(%22A%22%20%26amp%3B%20r%20-%201).Value%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22A%22%20%26amp%3B%20r).ClearContents%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20r%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2778491%22%20slang%3D%22en-US%22%3ERe%3A%20Clear%20repeating%20values%20in%20column%20A%2C%20keep%20rows%20intact%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778491%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164479%22%20target%3D%22_blank%22%3E%40Ranju0714%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3E'VBA%20code%0ASub%20ClearWithoutShiftRows()%0A%20%20%20%20rws%20%3D%20Cells(Rows.Count%2C%20%22A%22).End(xlUp).Row%0A%20%20%20%20For%20j%20%3D%20rws%20To%202%20Step%20-1%0A%20%20%20%20%20%20%20%20If%20Cells(j%2C%201).Value%20%3D%20Cells(j%20-%201%2C%201).Value%20Then%20Cells(j%2C%201).Value%20%3D%20%22%22%0A%20%20%20%20Next%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

 

bcd

 

cde

 

 

 

6 Replies
best response confirmed by Ranju0714 (New 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