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]

I ran it and it appears to have only removed 1 record, i sense checked and entered some duplicate data and it works how I expected it to. Thank you very much, I am going to hunt the 1 record in my non-copy version and found it, suprised that in 13k records there wasnt more!

For the pricing without customer that looks good to, however when i paste the list of item numbres into a new work sheet, then highlight it says 230 dups removed but nothing is.... any ideas? i did paste values only as well.

 

[Peter_SSs]

.. however when i paste the list of item numbres into a new work sheet, then highlight it says 230 dups removed but nothing is.... any ideas? i did paste values only as well.

I'm not sure what you are saying here.

1. My code does not 'report' anything, let alone how many rows were removed, so what is it that says "230 dupes removed"?

2. The code would need altering to do the second jobe if data is in different columns. I would need details/sample to make adjustments.

 

[Rob1200]

I'm not sure what you are saying here.

1. My code does not 'report' anything, let alone how many rows were removed, so what is it that says "230 dupes removed"?

2. The code would need altering to do the second jobe if data is in different columns. I would need details/sample to make adjustments.

Thats in reference to the other posters code, I used that where the customer does not exist. It worked fine I then went through and deleted any duplicates which the formula showed. I then took the item numbers and pasted into a new work sheet value only. I then highlighted and did remove duplicates and it said "230 dupes removed" even though the count doesnt go down.

Your code works fine for what I need, anyway to identify the one record it removed, spent 20 mins shuffling over the data now and it would save some time!

 

[Peter_SSs]

Thats in reference to the other posters code,

If more than 1 other person is involved, it's always best to use their name when replying, or quote a small part of their post like I have here, so that readers know who you are addressing.

.. I then highlighted and did remove duplicates ..

Did you note what I wrote about that earlier? It should not be relied upon.

Your code works fine for what I need, anyway to identify the one record it removed, spent 20 mins shuffling over the data now and it would save some time!

If you would like to see what my code is removing, that could be easily arranged.


If you'd care to share some details about the layout of the sheet that doesn't have customer names, I could provide an adjusted macro.

 

[AlanY]

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.

if Peter's solution works for you I would use it as it's much faster than the array formula.

fyi, the formula in post#15 column S will show blank if it's a duplicate, you can put a filter in Row 1, select and delete the rows with blank.

 

[Peter_SSs]

.. it's much faster than the array formula.

Especially since there is approx. 12,000 rows.