VBA to remove duplicates on multiple criteria

tourertt

New Member
Joined
Sep 4, 2014
Messages
11
Hello All,New member from Australia here and I have a question. I have tried search, but didn't find a topic addressing specifically what i need. I have a spreadsheet with Excel table in it with the list of invoices for the month.Date Customer Code Customer Name Amount1/09/2014 CUST01 Customer 1 1001/09/2014 CUST02 Customer 2 1501/09/2014 CUST03 Customer 3 2001/09/2014 CUST01 Customer 1 -1002/09/2014 CUST02 Customer 2 2503/09/2014 CUST03 Customer 3 -2004/09/2014 CUST04 Customer 4 300What i need is a VBA macro that will remove invoices and credits for the same customer, done on the same day.So, if Date matches, customer code matches, and amount is opposite ($100 invoice and -$100 credit note as in example above) i need to remove rows with both the invoice and the credit.Please help me with a code for it.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
For some reason I cannot edit my previous post, so I'll post a second one with a screenshot... Hello All, New member from Australia here and I have a question. I have tried search, but didn't find a topic addressing specifically what i need. I have a spreadsheet with Excel table in it with the list of invoices for the month What i need is a VBA macro that will remove invoices and credits for the same customer, done on the same day.So, if Date matches, customer code matches, and amount is opposite ($100 invoice and -$100 credit note as in example above) i need to remove rows with both the invoice and the credit.Please help me with a code for it.
shot-4_zpsd4c2560c.jpg
 
Upvote 0
tourertt,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Based on your instructions, there could be only 2 duplicate groups of cells, not, 3 or more.


Sample raw data:


Excel 2007
ABCD
1DateCustomer CodeCustomer NameAmount
21/09/2014CUST01Customer 1100
31/09/2014CUST02Customer 2150
41/09/2014CUST03Customer 3200
51/09/2014CUST01Customer 1-100
62/09/2014CUST02Customer 2250
73/09/2014CUST03Customer 3-200
84/09/2014CUST04Customer 4300
9
Sheet1


After the macro:


Excel 2007
ABCD
1DateCustomer CodeCustomer NameAmount
21/09/2014CUST02Customer 2150
31/09/2014CUST03Customer 3200
42/09/2014CUST02Customer 2250
53/09/2014CUST03Customer 3-200
64/09/2014CUST04Customer 4300
7
8
9
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub RemoveDupes()
' hiker95, 09/04/2014, ME803452
Dim r As Long, lr As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Columns(5).ClearContents
With Range("E2:E" & lr)
  .FormulaR1C1 = "=RC[-4]&RC[-3]&RC[-2]"
  .Value = .Value
End With
Range("A2:E" & lr).Sort key1:=Range("E2"), order1:=1
For r = 2 To lr
  n = Application.CountIf(Columns(5), Cells(r, 5).Value)
  If n = 2 Then
    If Cells(r, 4).Value + Cells(r + 1, 4).Value = 0 Then
      Range("A" & r & ":E" & r + 1).ClearContents
    End If
  End If
  r = r + n - 1
Next r
Range("A2:E" & lr).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:D" & lr).Sort key1:=Range("A2"), order1:=1, key2:=Range("B2"), order2:=1
Columns(5).ClearContents
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveDupes macro.
 
Upvote 0
It worked great hicker!2 more things...If i have an invoice, credit and another invoice for the same customer on the same day and the same amount, i want only one invoice and credit to be gone. With current code, it leaves all 3 untouched. Have a look at the screenshot.
shot-6_zpsf782acd5.jpg
Also, if no results match the criteria (no duplicates) i get error message:
shot-7_zps8ae1d8f7.jpg
 
Upvote 0
tourertt,

Please answer my questions.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


You are posting a picture/graphic, again. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.


In order to continue I will need real screenshots, or, your workbook/worksheet:


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
I have answered above, but will do it again:

I am using Windows 7, Excel 2010

Sorry for posting screenshots, I didn't know how to post the actual data. Now, trying to do it with Excel Jeanie. Please note, in the data file on 1/9/14 i have 3 entries for customer 1. Two +100 and one -100. From that, i need only one +100 and one -100 to be gone, leaving just one entry for +100.

Sheet1

ABCD
1DateCustomer CodeCustomer NameAmount
21/09/2014CUST01Customer 1100
31/09/2014CUST01Customer 1100
41/09/2014CUST02Customer 2150
51/09/2014CUST03Customer 3200
61/09/2014CUST01Customer 1-100
72/09/2014CUST02Customer 2250
83/09/2014CUST03Customer 3-200
94/09/2014CUST04Customer 4300
105/09/2014CUST05Customer 5301
115/09/2014CUST05Customer 5-301
126/09/2014CUST06Customer 6302
137/09/2014CUST07Customer 7303
148/09/2014CUST08Customer 8304
159/09/2014CUST09Customer 9305
1610/09/2014CUST10Customer 10306
1711/09/2014CUST11Customer 11307
1812/09/2014CUST11Customer 11-307
1912/09/2014CUST12Customer 12308
2013/09/2014CUST13Customer 13309
2114/09/2014CUST14Customer 14310
2215/09/2014CUST15Customer 15311
2316/09/2014CUST16Customer 16312
2417/09/2014CUST17Customer 17313
2518/09/2014CUST18Customer 18314
2619/09/2014CUST19Customer 19315
2720/09/2014CUST20Customer 20316
2821/09/2014CUST21Customer 21317
2922/09/2014CUST22Customer 22318
3022/09/2014CUST22Customer 22-318
3123/09/2014CUST23Customer 23319
3224/09/2014CUST24Customer 24320
3325/09/2014CUST25Customer 25321
3426/09/2014CUST26Customer 26322
3527/09/2014CUST27Customer 27323
3628/09/2014CUST28Customer 28324
3729/09/2014CUST29Customer 29325
3830/09/2014CUST30Customer 30326
391/10/2014CUST31Customer 31327
402/10/2014CUST32Customer 32328
413/10/2014CUST33Customer 33329
424/10/2014CUST34Customer 34330
435/10/2014CUST35Customer 35331

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 75px;"><col style="width: 103px;"><col style="width: 108px;"><col style="width: 57px;"></colgroup><tbody>
</tbody>
 
Upvote 0
tourertt,

The following is based on the new dataset.

Sample raw data:


Excel 2007
ABCD
1DateCustomer CodeCustomer NameAmount
21/9/2014CUST01Customer 1100
31/9/2014CUST01Customer 1100
41/9/2014CUST02Customer 2150
51/9/2014CUST03Customer 3200
61/9/2014CUST01Customer 1-100
72/9/2014CUST02Customer 2250
83/9/2014CUST03Customer 3-200
94/9/2014CUST04Customer 4300
105/9/2014CUST05Customer 5301
115/9/2014CUST05Customer 5-301
126/9/2014CUST06Customer 6302
137/9/2014CUST07Customer 7303
148/9/2014CUST08Customer 8304
159/9/2014CUST09Customer 9305
1610/9/2014CUST10Customer 10306
1711/9/2014CUST11Customer 11307
1812/9/2014CUST11Customer 11-307
1912/9/2014CUST12Customer 12308
2013/09/2014CUST13Customer 13309
2114/09/2014CUST14Customer 14310
2215/09/2014CUST15Customer 15311
2316/09/2014CUST16Customer 16312
2417/09/2014CUST17Customer 17313
2518/09/2014CUST18Customer 18314
2619/09/2014CUST19Customer 19315
2720/09/2014CUST20Customer 20316
2821/09/2014CUST21Customer 21317
2922/09/2014CUST22Customer 22318
3022/09/2014CUST22Customer 22-318
3123/09/2014CUST23Customer 23319
3224/09/2014CUST24Customer 24320
3325/09/2014CUST25Customer 25321
3426/09/2014CUST26Customer 26322
3527/09/2014CUST27Customer 27323
3628/09/2014CUST28Customer 28324
3729/09/2014CUST29Customer 29325
3830/09/2014CUST30Customer 30326
391/10/2014CUST31Customer 31327
402/10/2014CUST32Customer 32328
413/10/2014CUST33Customer 33329
424/10/2014CUST34Customer 34330
435/10/2014CUST35Customer 35331
44
Sheet1


After the new macro:


Excel 2007
ABCD
1DateCustomer CodeCustomer NameAmount
21/9/2014CUST01Customer 1100
31/9/2014CUST02Customer 2150
42/9/2014CUST02Customer 2250
51/9/2014CUST03Customer 3200
63/9/2014CUST03Customer 3-200
74/9/2014CUST04Customer 4300
86/9/2014CUST06Customer 6302
97/9/2014CUST07Customer 7303
108/9/2014CUST08Customer 8304
119/9/2014CUST09Customer 9305
1210/9/2014CUST10Customer 10306
1311/9/2014CUST11Customer 11307
1412/9/2014CUST11Customer 11-307
1512/9/2014CUST12Customer 12308
1613/09/2014CUST13Customer 13309
1714/09/2014CUST14Customer 14310
1815/09/2014CUST15Customer 15311
1916/09/2014CUST16Customer 16312
2017/09/2014CUST17Customer 17313
2118/09/2014CUST18Customer 18314
2219/09/2014CUST19Customer 19315
2320/09/2014CUST20Customer 20316
2421/09/2014CUST21Customer 21317
2523/09/2014CUST23Customer 23319
2624/09/2014CUST24Customer 24320
2725/09/2014CUST25Customer 25321
2826/09/2014CUST26Customer 26322
2927/09/2014CUST27Customer 27323
3028/09/2014CUST28Customer 28324
3129/09/2014CUST29Customer 29325
3230/09/2014CUST30Customer 30326
331/10/2014CUST31Customer 31327
342/10/2014CUST32Customer 32328
353/10/2014CUST33Customer 33329
364/10/2014CUST34Customer 34330
375/10/2014CUST35Customer 35331
38
39
40
41
42
43
44
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Sub RemoveDupes_V2()
' hiker95, 09/07/2014, ME803452
Dim r As Long, lr As Long, rr As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Columns(5).ClearContents
With Range("E2:E" & lr)
  .FormulaR1C1 = "=RC[-4]&RC[-3]&RC[-2]"
  .Value = .Value
End With
Range("A2:E" & lr).Sort key1:=Range("E2"), order1:=1
For r = 2 To lr
  n = Application.CountIf(Columns(5), Cells(r, 5).Value)
  If n = 2 Then
    If Cells(r, 4).Value + Cells(r + 1, 4).Value = 0 Then
      Range("A" & r & ":E" & r + 1).ClearContents
    End If
  ElseIf n > 2 Then
    For rr = r To r + n - 1
      If Cells(rr, 4).Value + Cells(rr + 1, 4).Value = 0 Then
        Range("A" & rr & ":E" & rr + 1).ClearContents
      End If
    Next rr
  End If
  r = r + n - 1
Next r
Range("A2:E" & lr).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:D" & lr).Sort key1:=Range("B2"), order1:=1, key2:=Range("A2"), order2:=1
Columns(5).ClearContents
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveDupes_V2 macro.
 
Upvote 0
Hiker,

Worked like magic! Thanks you very much. Can you modify the code to do the same in another report? Same principle, Date, Customer number, Amount. Just different layout. I have tried to figure out your code myself to transfer from 1st report to this one but failed.

ABCDEFG
1 Robayne Pty Ltd6314
2 NDF Daily Debtors Finance Report
3 Created:05 SEP 2014 05:23PM
4 Start Date:Friday, 5 September 2014
5 End Date:Friday, 5 September 2014
6 Date Used: Date Processed
7 Age Group(s): AD, B, BURN, DA, HEN, HO, LA, M, OP
8
9KEY1KEY2CUSTOMERNOCUSTOMERNAMEINVOICENODATEINVOICEAMOUNT
10DEBTFINANCE18PICK01PICKEN AUTO BODY REPAIR CENTRE (P,CS,K)CN2823205 Sep 2014-$1,042.80
11DEBTFINANCE21DYNA03DYNAMIC SIGNS & ENGRAVING (ES)CN2823505 Sep 2014-$682.00
12DEBTFINANCE23PALM01Palmerston Smash RepairsCN2823705 Sep 2014-$272.39
13DEBTFINANCE22HIGH03HIGHWAY SMASH REPAIRS P/LCN2823605 Sep 2014-$207.58
14DEBTFINANCE26DTHI00DT HILOAD AUSTRALIA PTY LTDCN2824105 Sep 2014-$191.07
15DEBTFINANCE24SGIT00S-Git- Automotive Repairs (P)CN2823805 Sep 2014-$77.57
16DEBTFINANCE17BODY02BODYCRAFT COLLISION REPAIRSCN2823105 Sep 2014-$31.20
17DEBTFINANCE19ROCK01ROCKINGHAM SMASH REPAIRSCN2823305 Sep 2014-$22.67
18DEBTFINANCE20ROCK01ROCKINGHAM SMASH REPAIRSCN2823405 Sep 2014-$22.67
19DEBTFINANCE25PALM01Palmerston Smash RepairsCN2824005 Sep 2014-$7.39
20DEBTFINANCE106ALPH02ALPHABLAST HENDERSONIV88905905 Sep 2014$4.11
21DEBTFINANCE67PICK01PICKEN AUTO BODY REPAIR CENTRE (P,CS,K)IV88895205 Sep 2014$10.43
22DEBTFINANCE94ROCK01ROCKINGHAM SMASH REPAIRSIV88901705 Sep 2014$22.67

<tbody>
</tbody>
 
Upvote 0
tourertt,

Thanks for the feedback.

You are very welcome. Glad I could help.


Can you modify the code to do the same in another report? Same principle, Date, Customer number, Amount. Just different layout.

Sure.

I just checked in to see if I had any responses today.

I will be back later today.

I am off to go fly fishing on the West Branch of the Delaware River, at Hale Eddy Bridge, and, Deposit, New York.
 
Upvote 0

Forum statistics

Threads
1,222,017
Messages
6,163,403
Members
451,835
Latest member
kristianb63

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
Back
Top