VBA to remove duplicates on multiple criteria

[tourertt]

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.

 

[tourertt]

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.

 

[hiker95]

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
	A	B	C	D
1	Date	Customer Code	Customer Name	Amount
2	1/09/2014	CUST01	Customer 1	100
3	1/09/2014	CUST02	Customer 2	150
4	1/09/2014	CUST03	Customer 3	200
5	1/09/2014	CUST01	Customer 1	-100
6	2/09/2014	CUST02	Customer 2	250
7	3/09/2014	CUST03	Customer 3	-200
8	4/09/2014	CUST04	Customer 4	300
9
Sheet1

After the macro:

Excel 2007
	A	B	C	D
1	Date	Customer Code	Customer Name	Amount
2	1/09/2014	CUST02	Customer 2	150
3	1/09/2014	CUST03	Customer 3	200
4	2/09/2014	CUST02	Customer 2	250
5	3/09/2014	CUST03	Customer 3	-200
6	4/09/2014	CUST04	Customer 4	300
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.

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.

 

[tourertt]

I am using Excel 2010 PC Version. Will try your macro now. Thanks!

 

[tourertt]

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.

Also, if no results match the criteria (no duplicates) i get error message:

 

[hiker95]

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.

 

[tourertt]

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

	A	B	C	D
1	Date	Customer Code	Customer Name	Amount
2	1/09/2014	CUST01	Customer 1	100
3	1/09/2014	CUST01	Customer 1	100
4	1/09/2014	CUST02	Customer 2	150
5	1/09/2014	CUST03	Customer 3	200
6	1/09/2014	CUST01	Customer 1	-100
7	2/09/2014	CUST02	Customer 2	250
8	3/09/2014	CUST03	Customer 3	-200
9	4/09/2014	CUST04	Customer 4	300
10	5/09/2014	CUST05	Customer 5	301
11	5/09/2014	CUST05	Customer 5	-301
12	6/09/2014	CUST06	Customer 6	302
13	7/09/2014	CUST07	Customer 7	303
14	8/09/2014	CUST08	Customer 8	304
15	9/09/2014	CUST09	Customer 9	305
16	10/09/2014	CUST10	Customer 10	306
17	11/09/2014	CUST11	Customer 11	307
18	12/09/2014	CUST11	Customer 11	-307
19	12/09/2014	CUST12	Customer 12	308
20	13/09/2014	CUST13	Customer 13	309
21	14/09/2014	CUST14	Customer 14	310
22	15/09/2014	CUST15	Customer 15	311
23	16/09/2014	CUST16	Customer 16	312
24	17/09/2014	CUST17	Customer 17	313
25	18/09/2014	CUST18	Customer 18	314
26	19/09/2014	CUST19	Customer 19	315
27	20/09/2014	CUST20	Customer 20	316
28	21/09/2014	CUST21	Customer 21	317
29	22/09/2014	CUST22	Customer 22	318
30	22/09/2014	CUST22	Customer 22	-318
31	23/09/2014	CUST23	Customer 23	319
32	24/09/2014	CUST24	Customer 24	320
33	25/09/2014	CUST25	Customer 25	321
34	26/09/2014	CUST26	Customer 26	322
35	27/09/2014	CUST27	Customer 27	323
36	28/09/2014	CUST28	Customer 28	324
37	29/09/2014	CUST29	Customer 29	325
38	30/09/2014	CUST30	Customer 30	326
39	1/10/2014	CUST31	Customer 31	327
40	2/10/2014	CUST32	Customer 32	328
41	3/10/2014	CUST33	Customer 33	329
42	4/10/2014	CUST34	Customer 34	330
43	5/10/2014	CUST35	Customer 35	331

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

 

[hiker95]

tourertt,

The following is based on the new dataset.

Sample raw data:

Excel 2007
	A	B	C	D
1	Date	Customer Code	Customer Name	Amount
2	1/9/2014	CUST01	Customer 1	100
3	1/9/2014	CUST01	Customer 1	100
4	1/9/2014	CUST02	Customer 2	150
5	1/9/2014	CUST03	Customer 3	200
6	1/9/2014	CUST01	Customer 1	-100
7	2/9/2014	CUST02	Customer 2	250
8	3/9/2014	CUST03	Customer 3	-200
9	4/9/2014	CUST04	Customer 4	300
10	5/9/2014	CUST05	Customer 5	301
11	5/9/2014	CUST05	Customer 5	-301
12	6/9/2014	CUST06	Customer 6	302
13	7/9/2014	CUST07	Customer 7	303
14	8/9/2014	CUST08	Customer 8	304
15	9/9/2014	CUST09	Customer 9	305
16	10/9/2014	CUST10	Customer 10	306
17	11/9/2014	CUST11	Customer 11	307
18	12/9/2014	CUST11	Customer 11	-307
19	12/9/2014	CUST12	Customer 12	308
20	13/09/2014	CUST13	Customer 13	309
21	14/09/2014	CUST14	Customer 14	310
22	15/09/2014	CUST15	Customer 15	311
23	16/09/2014	CUST16	Customer 16	312
24	17/09/2014	CUST17	Customer 17	313
25	18/09/2014	CUST18	Customer 18	314
26	19/09/2014	CUST19	Customer 19	315
27	20/09/2014	CUST20	Customer 20	316
28	21/09/2014	CUST21	Customer 21	317
29	22/09/2014	CUST22	Customer 22	318
30	22/09/2014	CUST22	Customer 22	-318
31	23/09/2014	CUST23	Customer 23	319
32	24/09/2014	CUST24	Customer 24	320
33	25/09/2014	CUST25	Customer 25	321
34	26/09/2014	CUST26	Customer 26	322
35	27/09/2014	CUST27	Customer 27	323
36	28/09/2014	CUST28	Customer 28	324
37	29/09/2014	CUST29	Customer 29	325
38	30/09/2014	CUST30	Customer 30	326
39	1/10/2014	CUST31	Customer 31	327
40	2/10/2014	CUST32	Customer 32	328
41	3/10/2014	CUST33	Customer 33	329
42	4/10/2014	CUST34	Customer 34	330
43	5/10/2014	CUST35	Customer 35	331
44
Sheet1

After the new macro:

Excel 2007
	A	B	C	D
1	Date	Customer Code	Customer Name	Amount
2	1/9/2014	CUST01	Customer 1	100
3	1/9/2014	CUST02	Customer 2	150
4	2/9/2014	CUST02	Customer 2	250
5	1/9/2014	CUST03	Customer 3	200
6	3/9/2014	CUST03	Customer 3	-200
7	4/9/2014	CUST04	Customer 4	300
8	6/9/2014	CUST06	Customer 6	302
9	7/9/2014	CUST07	Customer 7	303
10	8/9/2014	CUST08	Customer 8	304
11	9/9/2014	CUST09	Customer 9	305
12	10/9/2014	CUST10	Customer 10	306
13	11/9/2014	CUST11	Customer 11	307
14	12/9/2014	CUST11	Customer 11	-307
15	12/9/2014	CUST12	Customer 12	308
16	13/09/2014	CUST13	Customer 13	309
17	14/09/2014	CUST14	Customer 14	310
18	15/09/2014	CUST15	Customer 15	311
19	16/09/2014	CUST16	Customer 16	312
20	17/09/2014	CUST17	Customer 17	313
21	18/09/2014	CUST18	Customer 18	314
22	19/09/2014	CUST19	Customer 19	315
23	20/09/2014	CUST20	Customer 20	316
24	21/09/2014	CUST21	Customer 21	317
25	23/09/2014	CUST23	Customer 23	319
26	24/09/2014	CUST24	Customer 24	320
27	25/09/2014	CUST25	Customer 25	321
28	26/09/2014	CUST26	Customer 26	322
29	27/09/2014	CUST27	Customer 27	323
30	28/09/2014	CUST28	Customer 28	324
31	29/09/2014	CUST29	Customer 29	325
32	30/09/2014	CUST30	Customer 30	326
33	1/10/2014	CUST31	Customer 31	327
34	2/10/2014	CUST32	Customer 32	328
35	3/10/2014	CUST33	Customer 33	329
36	4/10/2014	CUST34	Customer 34	330
37	5/10/2014	CUST35	Customer 35	331
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).

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.

 

[tourertt]

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.

	A	B	C	D	E	F	G
1			Robayne Pty Ltd	6314
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
9	KEY1	KEY2	CUSTOMERNO	CUSTOMERNAME	INVOICENO	DATE	INVOICEAMOUNT
10	DEBTFINANCE	18	PICK01	PICKEN AUTO BODY REPAIR CENTRE (P,CS,K)	CN28232	05 Sep 2014	-$1,042.80
11	DEBTFINANCE	21	DYNA03	DYNAMIC SIGNS & ENGRAVING (ES)	CN28235	05 Sep 2014	-$682.00
12	DEBTFINANCE	23	PALM01	Palmerston Smash Repairs	CN28237	05 Sep 2014	-$272.39
13	DEBTFINANCE	22	HIGH03	HIGHWAY SMASH REPAIRS P/L	CN28236	05 Sep 2014	-$207.58
14	DEBTFINANCE	26	DTHI00	DT HILOAD AUSTRALIA PTY LTD	CN28241	05 Sep 2014	-$191.07
15	DEBTFINANCE	24	SGIT00	S-Git- Automotive Repairs (P)	CN28238	05 Sep 2014	-$77.57
16	DEBTFINANCE	17	BODY02	BODYCRAFT COLLISION REPAIRS	CN28231	05 Sep 2014	-$31.20
17	DEBTFINANCE	19	ROCK01	ROCKINGHAM SMASH REPAIRS	CN28233	05 Sep 2014	-$22.67
18	DEBTFINANCE	20	ROCK01	ROCKINGHAM SMASH REPAIRS	CN28234	05 Sep 2014	-$22.67
19	DEBTFINANCE	25	PALM01	Palmerston Smash Repairs	CN28240	05 Sep 2014	-$7.39
20	DEBTFINANCE	106	ALPH02	ALPHABLAST HENDERSON	IV889059	05 Sep 2014	$4.11
21	DEBTFINANCE	67	PICK01	PICKEN AUTO BODY REPAIR CENTRE (P,CS,K)	IV888952	05 Sep 2014	$10.43
22	DEBTFINANCE	94	ROCK01	ROCKINGHAM SMASH REPAIRS	IV889017	05 Sep 2014	$22.67

<tbody>
</tbody>

 

[hiker95]

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.