Clear cell data if rows match

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
181
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Here you go ;)

VBA Code:
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)
 
Solution

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
In case you need to sort first:

VBA Code:
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

Board Regular
Joined
Sep 30, 2017
Messages
181
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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

Board Regular
Joined
Sep 30, 2017
Messages
181
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
In case you need to sort first:

VBA Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,825
Messages
5,766,661
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top