Struggling With Duplicates

[Rob1200]

Hi guys

Long time lurker, got some really useful info from this site.

I have had a look round regarding removing duplicates for my needs but cannot find a precise match and I need to get this resolved ASAP.

I have an extract of data with multiple columns

Column A contains item number
Column B contains customer name
Column C contains the date valid
Column D contains the price

The way I have been provided the data I need to make sure there are only unique items per customer using the newest date example below

Item1 - London Clinic Hospital - 01/01/14 - 50
Item1 - London Clinic Hospital - 01/01/15 - 60
Item1 - London Clinic Hospital - 01/01/16 - 70


So I need a way to remove safely the 2014 and 2015 records and leave only the newest record i.e. in this example 2016

Any help much appreciated

Thanks

 

[Rob1200]

Can I confirm that it is just the latest date for each Customer that we need to keep, no matter the Item code?
So for ALDER001, we only need to keep that top row of those 6 rows for that customer?


(BTW, you cannot attach files but to post slightly better screen shots, see my signature block below.)


Edit: I don't want or need your actual sheet with 12,000 rows. What you have given should be fine, apart from maybe a question or two more.

Correct, the most recent date needs to be kept when there are same item numbers for the same customer.

For customer ALDER001 - as there are no duplicate rows for the same item all 6 would be kept.

If the same item appeared twice, so two rows, then the one with the newest date needs to be kept.

 

[Rob1200]

they are array formula, enter without the curly brackets {} and with Ctrl+Shift+Enter.

that wouldn't work for you anyway as I've posted before your explanation in post #5.

your formula with no customer number did work however and returned me a value. i sorted via item number so that it groups them together and just manually removing the older entry

 

[Peter_SSs]

..the most recent date needs to be kept when there are same item numbers for the same customer.

For customer ALDER001 - as there are no duplicate rows for the same item all 6 would be kept.

So the sample data provided is not very representative as there are no rows to delete in the whole sample?
Some sample data where there are at least some deletions to be done would have been more useful.

Should have something shortly.

 

[Peter_SSs]

Try this in a copy of your workbook. This is for the first problem.

Sub KeepLatestCustomer()
  Dim nc As Long
  
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  Application.ScreenUpdating = False
  With Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, nc)
    .Sort Key1:=.Columns(2), Order1:=xlAscending, Key2:=.Columns(1), Order2:=xlAscending, Key3:=.Columns(3), Order3:=xlDescending, Header:=xlNo, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
    .Columns(nc).Value = Evaluate(Replace(Replace(Replace(Replace("if(#=@,if(%=^,1,""""),"""")", _
                            "#", .Columns(2).Address), "@", .Columns(2).Offset(-1).Address), "%", .Columns(1).Address), "^", .Columns(1).Offset(-1).Address))
    .Sort Key1:=.Cells(1, nc), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    On Error Resume Next
    .Columns(nc).SpecialCells(xlConstants, xlNumbers).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

 

[AlanY]

second try, highlighted green cells are altered for testing.
again, its array formula enter with shift-control-enter without the {} brackets.

Excel 2012
	A	B	C	D	E	Q	R	S
1	Item Code	Customer	Date		Price	Present in F4101
2	DWF614B	ABBEY001		02/10/2015	1,525	DWF614B		DWF614B
3	STDLOAN	ABBEY001			0	#N/A		STDLOAN
4	CSI07	ALDER001		04/08/2013	525	CSI07		CSI07
5	CSI08	ALDER001		04/08/2013	525	CSI08		CSI08
6	CSI09	ALDER001		04/08/2013	525	CSI09
7	CSI09	ALDER001		06/08/2013	525	CSI10
8	CSI09	ALDER001		08/08/2013	525	CSI11
9	CSI09	ALDER001		10/08/2013	525	CSI12		CSI09
10	DWH039	BMI009		02/10/2015	1,200	DWH039		DWH039
11	DWH041	BMI009		02/10/2015	1,200	DWH041		DWH041
12	DWH043	BMI009		02/10/2015	1,200	DWH043		DWH043
13	DWH046	BMI009		02/10/2015	1,200	DWH046		DWH046
14	DWH048	BMI009		02/10/2015	1,200	DWH048		DWH048
15	DWH050	BMI009		02/10/2015	1,200	DWH050		DWH050
16	DWH139	BMI009		02/10/2015	1,200	DWH139		DWH139
17	DWH141	BMI009		02/10/2015	1,200	DWH141
18	DWH141	BMI009		03/10/2015	1,200	DWH143
19	DWH141	BMI009		04/10/2015	1,200	DWH146
20	DWH148	BMI009		02/10/2015	1,200	DWH148		DWH148
21	DWH150	BMI009		02/10/2015	1,200	DWH150		DWH150
22	BCP030320	BURN001		12/10/2012	750	#N/A		BCP030320
23	NSO2713	CHARI001		01/07/2016	88	#N/A		NSO2713
24	DWH141	BMI009		04/11/2015	88	#N/A		DWH141
25	VSO011	CHARI002		09/08/2014	144	#N/A		VSO011
26	VSO012	CHARI002		09/08/2014	144	#N/A		VSO012
Sheet1

Cell Formulas
Range	Formula
S2	{=IF($D2=MAX(IF($A$2:$A$26=$A2,IF($B$2:$B$26=B2,$D$2:$D$26))),$A2,"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AlanY]

similarily for data without customer name

{=IF($D2=MAX(IF($A$2:$A$26=$A2,$D$2:$D$26)),$A2,"")}

 

[Rob1200]

Try this in a copy of your workbook. This is for the first problem.

Sub KeepLatestCustomer()
  Dim nc As Long
  
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  Application.ScreenUpdating = False
  With Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, nc)
    .Sort Key1:=.Columns(2), Order1:=xlAscending, Key2:=.Columns(1), Order2:=xlAscending, Key3:=.Columns(3), Order3:=xlDescending, Header:=xlNo, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
    .Columns(nc).Value = Evaluate(Replace(Replace(Replace(Replace("if(#=@,if(%=^,1,""""),"""")", _
                            "#", .Columns(2).Address), "@", .Columns(2).Offset(-1).Address), "%", .Columns(1).Address), "^", .Columns(1).Offset(-1).Address))
    .Sort Key1:=.Cells(1, nc), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    On Error Resume Next
    .Columns(nc).SpecialCells(xlConstants, xlNumbers).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

I pasted that into view code on the worksheet and ran, what should i expect to happen or see?

forgive my lack of understanding

 

[Rob1200]

without customer number appears to be fine, however when i then filter on the item numbre column and remove duplicates i get 230 duplicates removed but nothing is actually deleted the row count remains the same, very strange.

 

[Peter_SSs]

I pasted that into view code on the worksheet and ran, what should i expect to happen or see?

forgive my lack of understanding

You should expect to see any duplicates gone.

However, I think you don't have the code in the best place. Ideally it should be in a standard module, so once in the code window use the menu to Insert|Module and move the code to that module (probably Module1)

You'll need to save as a macro-enabled workbook and ensure you do have macros enabled.

Go back to your worksheet and press Alt+F8 to bring up the macro dialog, choose the macro and click 'Run'.

Here's my altered sample data with a few duplicates highlighted.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Item Code	Customer	Date	Price							Present in F4101
2	DWF614B	ABBEY001	10/2/2015	1,525	No	Yes	Customer	1	EACH	Yes	DWF614B
3	STDLOAN	ABBEY001		0	No	Yes	Customer	0		Yes	#N/A
4	CSI07	ALDER001	6/4/2013	525	No	Yes	Customer	1	EACH	Yes	CSI07
5	CSI08	ALDER001	8/4/2013	525	No	Yes	Customer	1	EACH	Yes	CSI08
6	CSI08	ALDER001	8/4/2013	525	No	Yes	Customer	1	EACH	Yes	CSI09
7	CSI08	ALDER001	7/4/2013	525	No	Yes	Customer	1	EACH	Yes	CSI10
8	CSI07	ALDER001	8/4/2013	525	No	Yes	Customer	1	EACH	Yes	CSI11
9	CSI12	ALDER001	8/4/2013	525	No	Yes	Customer	1	EACH	Yes	CSI12
10	DWH039	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH039
11	DWH041	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH041
12	DWH043	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH043
13	DWH046	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH046
14	DWH046	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH048
15	DWH046	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH050
16	DWH139	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH139
17	DWH141	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH141
18	BCP030320	BURN001	10/12/2012	750	No	Yes	Customer	1	EACH	Yes	#N/A
19	NSO2713	CHARI001	31/1/2014	88	No	Yes	Customer	1	EACH	Yes	#N/A
20	NSO2713	CHARI001	31/1/2016	88	No	Yes	Customer	1	EACH	Yes	#N/A
21	VSO011	CHARI002	8/9/2014	144	No	Yes	Customer	1	EACH	Yes	#N/A
22	VSO012	CHARI002	8/9/2014	144	No	Yes	Customer	1	EACH	Yes	#N/A
Sheet1

Here it is again after the code:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Item Code	Customer	Date	Price							Present in F4101
2	DWF614B	ABBEY001	10/2/2015	1,525	No	Yes	Customer	1	EACH	Yes	DWF614B
3	STDLOAN	ABBEY001		0	No	Yes	Customer	0		Yes	#N/A
4	CSI07	ALDER001	8/4/2013	525	No	Yes	Customer	1	EACH	Yes	CSI11
5	CSI08	ALDER001	8/4/2013	525	No	Yes	Customer	1	EACH	Yes	CSI08
6	CSI12	ALDER001	8/4/2013	525	No	Yes	Customer	1	EACH	Yes	CSI12
7	DWH039	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH039
8	DWH041	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH041
9	DWH043	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH043
10	DWH046	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH046
11	DWH139	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH139
12	DWH141	BMI009	10/2/2015	1,200	No	Yes	Customer	1	EACH	Yes	DWH141
13	BCP030320	BURN001	10/12/2012	750	No	Yes	Customer	1	EACH	Yes	#N/A
14	NSO2713	CHARI001	31/1/2016	88	No	Yes	Customer	1	EACH	Yes	#N/A
15	VSO011	CHARI002	8/9/2014	144	No	Yes	Customer	1	EACH	Yes	#N/A
16	VSO012	CHARI002	8/9/2014	144	No	Yes	Customer	1	EACH	Yes	#N/A
Sheet1

 

[Peter_SSs]

when i... and remove duplicates...

If you are using Excel's Remove Duplicates from the Data ribbon, then I recommend you stop doing that as it is notoriously unreliable. For a simple, repeatable example of its failure, see post #8 here.