How to clear duplicate values not delete the rows

[Arinoum]

Hi,
Is there a way to clear duplicate values without deleting the rows? I want the unique value to repeat only once and all below duplicates cleared from the cell not deleted. In the below example all information repeats but I only want it to repeat once for columns Name and Sales Person where originally this document repeated the name and Sales person down until a new number in the number column started.
Any help is greatly appreciated. Thanks!

Number	date	Name	Sales Person
123	4/20/21	Capital One	John
123	4/20/21
123	4/20/21
124	4/19/21	BOFA	Kelly
124	4/19/21
124	4/19/21
124	4/19/21

 

[Marc L]

Hi,​

​

differents ways but as you forgot to indicate the range address …​

 

[Arinoum]

Hi,​

​

differents ways but as you forgot to indicate the range address …​

C2 to D8. The original document repeats all information across the rows. I only want the info to repeat once. Thank you!

Number	date	Name	Sales Person
123	4/20/21	Capital One	John
123	4/20/21	Capital One	John
123	4/20/21	Capital One	John
124	4/19/21	BOFA	Kelly
124	4/19/21	BOFA	Kelly
124	4/19/21	BOFA	Kelly
124	4/19/21	BOFA	Kelly

 

[Marc L]

A demonstration as a beginner starter :​

Sub Demo1()
    With [A1].CurrentRegion.Columns("C:D")
            V = .Value2
        For R& = .Rows.Count To 3 Step -1
            If V(R, 1) = V(R - 1, 1) Then V(R, 1) = Empty
            If V(R, 2) = V(R - 1, 2) Then V(R, 2) = Empty
        Next
           .Value2 = V
    End With
End Sub

 

[Arinoum]

A demonstration as a beginner starter :​

Sub Demo1()
    With [A1].CurrentRegion.Columns("C:D")
            V = .Value2
        For R& = .Rows.Count To 3 Step -1
            If V(R, 1) = V(R - 1, 1) Then V(R, 1) = Empty
            If V(R, 2) = V(R - 1, 2) Then V(R, 2) = Empty
        Next
           .Value2 = V
    End With
End Sub

It worked! Thank you so much!

 

[Arinoum]

A demonstration as a beginner starter :​

Sub Demo1()
    With [A1].CurrentRegion.Columns("C:D")
            V = .Value2
        For R& = .Rows.Count To 3 Step -1
            If V(R, 1) = V(R - 1, 1) Then V(R, 1) = Empty
            If V(R, 2) = V(R - 1, 2) Then V(R, 2) = Empty
        Next
           .Value2 = V
    End With
End Sub

Is there a way to edit this to only delete duplicate values once a new number is introduced in column A? In the below example I would like the duplicate values in column C and D to only repeat once there is a new number on column A. In this case only show the value in C2 and D2 and then again C5 and D5 since the number in column A changed. Is this possible?

Number	date	Name	Sales Person
123	4/20/21	Capital One	John
123	4/20/21	Capital One	John
123	4/20/21	Capital One	John
124	4/19/21	Capital One	John
124	4/19/21	Capital One	John
124	4/19/21	Capital One	John
124	4/19/21	Capital One	John

 

[Marc L]

Yes, my revamped demonstration :​

Sub Demo1r()
    With [A1].CurrentRegion.Columns("C:D")
            V = .Value2
            W = .Item(-1).Value2
        For R& = .Rows.Count To 3 Step -1
            If W(R, 1) = W(R - 1, 1) Then
                If V(R, 1) = V(R - 1, 1) Then V(R, 1) = Empty
                If V(R, 2) = V(R - 1, 2) Then V(R, 2) = Empty
            End If
        Next
           .Value2 = V
    End With
End Sub

 

[Rick Rothstein]

Here is a way to write a macro that does not use any loops...

Sub Demo2()
  Dim LR As Long
  LR = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:D" & LR) = Evaluate("IF(A2:A" & LR & "<>A1:A" & LR - 1 & ",C2:D" & LR & ","""")")
End Sub

 

[Marc L]

Yes I badly revised my demonstration according to only the column A as key it needs a single If …​

 

[Arinoum]

Here is a way to write a macro that does not use any loops...

Sub Demo2()
  Dim LR As Long
  LR = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:D" & LR) = Evaluate("IF(A2:A" & LR & "<>A1:A" & LR - 1 & ",C2:D" & LR & ","""")")
End Sub

Thank you so much!