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]

That sounds amasing, hiker! How was the fishing??

 

[hiker95]

tourertt,

How was the fishing??

I had checked yesterday, and, early today, the USGS 01426500 WEST BRANCH DELAWARE RIVER AT HALE EDDY NY water depth height = 2.7 feet, but by the time I got there (tons of road upgrades) the water was too deep, 3.0+ feet, for me to do any wading/fly fishing with my bad leg. But the day was clear, low humidity, and, low temperature. I was just great to be out in the fresh air.


New sample raw data:

Excel 2007
	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	5-Sep-14	($1,042.80)
11	DEBTFINANCE	21	DYNA03	DYNAMIC SIGNS & ENGRAVING (ES)	CN28235	5-Sep-14	($682.00)
12	DEBTFINANCE	23	PALM01	Palmerston Smash Repairs	CN28237	5-Sep-14	($272.39)
13	DEBTFINANCE	22	HIGH03	HIGHWAY SMASH REPAIRS P/L	CN28236	5-Sep-14	($207.58)
14	DEBTFINANCE	26	DTHI00	DT HILOAD AUSTRALIA PTY LTD	CN28241	5-Sep-14	($191.07)
15	DEBTFINANCE	24	SGIT00	S-Git- Automotive Repairs (P)	CN28238	5-Sep-14	($77.57)
16	DEBTFINANCE	17	BODY02	BODYCRAFT COLLISION REPAIRS	CN28231	5-Sep-14	($31.20)
17	DEBTFINANCE	19	ROCK01	ROCKINGHAM SMASH REPAIRS	CN28233	5-Sep-14	($22.67)
18	DEBTFINANCE	20	ROCK01	ROCKINGHAM SMASH REPAIRS	CN28234	5-Sep-14	($22.67)
19	DEBTFINANCE	25	PALM01	Palmerston Smash Repairs	CN28240	5-Sep-14	($7.39)
20	DEBTFINANCE	106	ALPH02	ALPHABLAST HENDERSON	IV889059	5-Sep-14	$4.11
21	DEBTFINANCE	67	PICK01	PICKEN AUTO BODY REPAIR CENTRE (P,CS,K)	IV888952	5-Sep-14	$10.43
22	DEBTFINANCE	94	ROCK01	ROCKINGHAM SMASH REPAIRS	IV889017	5-Sep-14	$22.67
23
Sheet1

After the new macro:

Excel 2007
	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	106	ALPH02	ALPHABLAST HENDERSON	IV889059	5-Sep-14	$4.11
11	DEBTFINANCE	17	BODY02	BODYCRAFT COLLISION REPAIRS	CN28231	5-Sep-14	($31.20)
12	DEBTFINANCE	26	DTHI00	DT HILOAD AUSTRALIA PTY LTD	CN28241	5-Sep-14	($191.07)
13	DEBTFINANCE	21	DYNA03	DYNAMIC SIGNS & ENGRAVING (ES)	CN28235	5-Sep-14	($682.00)
14	DEBTFINANCE	22	HIGH03	HIGHWAY SMASH REPAIRS P/L	CN28236	5-Sep-14	($207.58)
15	DEBTFINANCE	23	PALM01	Palmerston Smash Repairs	CN28237	5-Sep-14	($272.39)
16	DEBTFINANCE	25	PALM01	Palmerston Smash Repairs	CN28240	5-Sep-14	($7.39)
17	DEBTFINANCE	18	PICK01	PICKEN AUTO BODY REPAIR CENTRE (P,CS,K)	CN28232	5-Sep-14	($1,042.80)
18	DEBTFINANCE	67	PICK01	PICKEN AUTO BODY REPAIR CENTRE (P,CS,K)	IV888952	5-Sep-14	$10.43
19	DEBTFINANCE	19	ROCK01	ROCKINGHAM SMASH REPAIRS	CN28233	5-Sep-14	($22.67)
20	DEBTFINANCE	24	SGIT00	S-Git- Automotive Repairs (P)	CN28238	5-Sep-14	($77.57)
21
22
23
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_V3()
' hiker95, 09/08/2014, ME803452
Dim r As Long, lr As Long, rr As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 3).End(xlUp).Row
Columns(8).ClearContents
With Range("H10:H" & lr)
  .FormulaR1C1 = "=RC[-2]&RC[-5]&RC[-4]"
  .Value = .Value
End With
Range("A10:H" & lr).Sort key1:=Range("H10"), order1:=1
For r = 10 To lr
  n = Application.CountIf(Columns(8), Cells(r, 8).Value)
  If n = 2 Then
    If Cells(r, 8).Value = Cells(r + 1, 8).Value = 0 Then
      Range("A" & r & ":H" & r + 1).ClearContents
    End If
  ElseIf n > 2 Then
    For rr = r To r + n - 1
      If Cells(rr, 7).Value + Cells(rr + 1, 7).Value = 0 Then
        Range("A" & rr & ":H" & rr + 1).ClearContents
      End If
    Next rr
  End If
  r = r + n - 1
Next r
Range("A10:H" & lr).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A10:H" & lr).Sort key1:=Range("C10"), order1:=1, key2:=Range("F10"), order2:=1
Columns(8).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_V3 macro.

 

[tourertt]

Good day outside is what we all need sometimes
The code in your last post worked well, but it struggled to remove dupes in situation like below

Excel Workbook
	A	B	C	D	E	F	G
9	KEY1	KEY2	CUSTOMERNO	CUSTOMERNAME	INVOICENO	DATE	INVOICEAMOUNT
12	DEBTFINANCE	5	AUTO09	AUTO CARE PANEL WORKS (CS)	CN28255	09 Sep 2014	-$249.87
124	DEBTFINANCE	53	AUTO09	AUTO CARE PANEL WORKS (CS)	IV889491	09 Sep 2014	$249.87
125	DEBTFINANCE	67	AUTO09	AUTO CARE PANEL WORKS (CS)	IV889509	09 Sep 2014	$249.87
198			Total				$249.87
DEBTFINANCE

 

[hiker95]

tourertt,

We have gone thru three versions of the macro, because, you keep changing what the dataset looks like.

Now you introduce a new dataset with Subtotals?????


I have far exceeded the normal amount of time I allocate for solving problems/requests from web sites like MrExcel.


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.