Clear cell data if rows match

[muhleebbin]

Hi,

I have a data set from my website of customer orders, however I need to manipulate it so that it omits duplicate entries which is throwing off the pivot table calculations.

Basically column A has the order numbers and there are often times duplicate entries, column O has the amount paid but it is the same for both (sometimes multiple rows) so it accounts for the amount twice (or however many times it is repeated. Is it possible to make a macro that finds matching order numbers and then only keeping the first instance of the amount in Column O?

Hope this makes sense and thank you in advance for your help!

 

[mrshl9898]

Here you go

Sub doit()

Dim rownum As Long
Dim myorder As String
Dim mydate As String

rownum = 2

Do Until Cells(rownum, 1) = ""
    If Cells(rownum, 1) = Cells(rownum - 1, 1) And Cells(rownum, 4) = Cells(rownum - 1, 4) Then
    Cells(rownum, 15).ClearContents
    rownum = rownum + 1
    Else
    rownum = rownum + 1
    End If
Loop

End Sub

*note that requires the dates and orders to be in order (so duplicates are below one another)

 

[mrshl9898]

In case you need to sort first:

Sub SortMultipleColumns()
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("A1"), Order:=xlAscending
     .SortFields.Add Key:=Range("D1"), Order:=xlAscending
     .SetRange Range("A:AW")
     .Header = xlYes
     .Apply
End With
End Sub

 

[muhleebbin]

That worked perfectly! Thank you very much for taking the time to help solve this problem, I truly appreciate it as it will expedite a very tedious task into seconds.

 

[muhleebbin]

In case you need to sort first:

Sub SortMultipleColumns()
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("A1"), Order:=xlAscending
     .SortFields.Add Key:=Range("D1"), Order:=xlAscending
     .SetRange Range("A:AW")
     .Header = xlYes
     .Apply
End With
End Sub

Thanks for including this. Luckily this data does come presorted but i'm sure this will be helpful to me and/or others in the future.