Struggling With Duplicates

Rob1200

Board Regular
Joined
Mar 4, 2016
Messages
50
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Rob1200

Board Regular
Joined
Mar 4, 2016
Messages
50
Seems I cannot edit my post but to add, I then also have another sheet where I dont have a customer name, just item, price and date and need the same logic applied, all historical dates removed for the item leaving just the newest.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,284
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

1. About how many rows of data do you have?

2. Does this data contain any formulas that need to be retained?

3. Do you want to
- actually remove the old data, or
- provide just the latest data to another pert of the same worksheet or another worksheet?

4. Is there only 4 columns altogether?

5. Can you confirm that the date column are "real Excel dates"? That is, are they numbers formatted as dates, or are they just text values? What does =ISNUMBER(C3) return?

6. Are you looking for a macro to do this or perhaps steps to follow to do it manually?

7. Is this a task that will be repeated regularly?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,284
Office Version
  1. 365
Platform
  1. Windows
Seems I cannot edit my post ...
Yes, that is a restriction for new members - it is an anti-spam technique. Once you have been around for a while and made sufficient legitimate contribution to the board you will be able to edit - but only for 10 minutes after making each post.
 

Rob1200

Board Regular
Joined
Mar 4, 2016
Messages
50

ADVERTISEMENT

Hey Peter

Thanks for the quick response, I cant seem to attach the file or would.

I've done a paste below and to answer your questions

1. About 12,000 in one example
2.The last column could do with being retained but no big deal if not.
3. Remove the old data
4. There are some other columns but the data is static for those and I can cleanse before.
5. Returns true where a date is present - again i can cleanse the blanks
6. It's alot of data so whatever provides the most accurate result
7. Correct - but its probably going to need repeating at most another 5 times.

Hopefully the below summarises it a bit better, if the item number appears with multiple entries against the same customer I cannot do a straight remove duplicates. I sorted the date by newest first and then looked to remove that way but it didnt give me the flexibility I needed.



Item CodeCustomerDatePricePresent in F4101
DWF614BABBEY00110/02/20151,525NoYesCustomer1EACHYesDWF614B
STDLOANABBEY0010NoYesCustomer0Yes#N/A
CSI07ALDER00108/04/2013525NoYesCustomer1EACHYesCSI07
CSI08ALDER00108/04/2013525NoYesCustomer1EACHYesCSI08
CSI09ALDER00108/04/2013525NoYesCustomer1EACHYesCSI09
CSI10ALDER00108/04/2013525NoYesCustomer1EACHYesCSI10
CSI11ALDER00108/04/2013525NoYesCustomer1EACHYesCSI11
CSI12ALDER00108/04/2013525NoYesCustomer1EACHYesCSI12
DWH039BMI00910/02/20151,200NoYesCustomer1EACHYesDWH039
DWH041BMI00910/02/20151,200NoYesCustomer1EACHYesDWH041
DWH043BMI00910/02/20151,200NoYesCustomer1EACHYesDWH043
DWH046BMI00910/02/20151,200NoYesCustomer1EACHYesDWH046
DWH048BMI00910/02/20151,200NoYesCustomer1EACHYesDWH048
DWH050BMI00910/02/20151,200NoYesCustomer1EACHYesDWH050
DWH139BMI00910/02/20151,200NoYesCustomer1EACHYesDWH139
DWH141BMI00910/02/20151,200NoYesCustomer1EACHYesDWH141
DWH143BMI00910/02/20151,200NoYesCustomer1EACHYesDWH143
DWH146BMI00910/02/20151,200NoYesCustomer1EACHYesDWH146
DWH148BMI00910/02/20151,200NoYesCustomer1EACHYesDWH148
DWH150BMI00910/02/20151,200NoYesCustomer1EACHYesDWH150
BCP030320BURN00110/12/2012750NoYesCustomer1EACHYes#N/A
NSO2713CHARI00131/01/201488NoYesCustomer1EACHYes#N/A
NSO2715CHARI00131/01/201488NoYesCustomer1EACHYes#N/A
VSO011CHARI00208/09/2014144NoYesCustomer1EACHYes#N/A
VSO012CHARI00208/09/2014144NoYesCustomer1EACHYes#N/A

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this
F1 with customer name, G1 without


Excel 2012
ABCDEFG
1Item1London Clinic Hospital01/01/20145001/01/201501/01/2016
2Item1London Clinic Hospital01/01/201560
3Item1London Clinic01/01/201670
Sheet1
Cell Formulas
RangeFormula
F1{=MAX(IF($A$1:$A$6=$A$1,IF($B$1:$B$6=$B$1,$C$1:$C$6)))}
G1{=MAX(IF($A$1:$A$6=A1,$C$1:$C$6))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Rob1200

Board Regular
Joined
Mar 4, 2016
Messages
50

ADVERTISEMENT

All I returned was a bunch of zero's for the F1 formula
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,284
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
All I returned was a bunch of zero's for the F1 formula

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.
 
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,163,787
Messages
5,833,696
Members
430,225
Latest member
RKB_Excel

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