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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

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
47,926
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
47,926
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,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this
F1 with customer name, G1 without

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Item1</td><td style=";"> London Clinic Hospital </td><td style="text-align: right;;">01/01/2014</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/01/2015</td><td style="text-align: right;;">01/01/2016</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Item1</td><td style=";"> London Clinic Hospital </td><td style="text-align: right;;">01/01/2015</td><td style="text-align: right;;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Item1</td><td style=";"> London Clinic</td><td style="text-align: right;;">01/01/2016</td><td style="text-align: right;;">70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F1</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">$A$1:$A$6=$A$1,IF(<font color="Green">$B$1:$B$6=$B$1,$C$1:$C$6</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G1</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">$A$1:$A$6=A1,$C$1:$C$6</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

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
47,926
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,238
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

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