Remove duplicate values based on a date criteria

**nomis6565** · 06-19-2013, 05:17 PM

Hi,

I have a database of +2000 rows on a sheet that contain duplicated value on column "A". that I would like to delete based on the most recent date on collumn "H". (see the example file in attachment)

Thanks for your help!

Simon

**amotto11** · 06-19-2013, 05:28 PM

you could use a max(if(...)) array function to mark all the lines that need to be deleted, then sort by those and then delete them. Other than this i would think the only way to do this is via a macro. I am not able to look at your attachment at the moment on my computer, so i will give you what you would need for a formula in Cell "I2"

Put this in I2 or wherever your data starts:

=IF(MAX(IF(A2:A2000=A2,H2:H2000))=H2,0,1) 'Modify this formula as needed

To enter this formula hold Ctrl+Shift and press enter then copy the formula down and filter and delete as needed. HTH

**oeldere** · 06-19-2013, 05:37 PM

Filter on the green cells, and delete these values if the tekst is "delete".

See the attached file.

**nomis6565** · 06-19-2013, 07:41 PM

Hi,

Your solution partially works on collumn `L`, it identify the most recent date. The problem is that it works only for the values that are duplicate more than 2 times. It doesn't work for the one that are duplicate 2 times.

Thanks for your help,

**amotto11** · 06-19-2013, 10:22 PM

It should work for all of them, I believe. If you have one number it is the maximum. If you have two there still has to be a maximum, and three and so on. You will need to lock down, olace dollar signs, on the h values and the a values not including a2. Did you do this? I forgot it in my formula. That is probably the prpblen

**amotto11** · 06-19-2013, 10:24 PM

Here is the new formula remember to enter as an array

=IF(MAX(IF($A$2:$A$2000=A2,$H$2:$H$2000))=H2,0,1) 'Modify this formula as needed

I think it didn't work because the values weren't locked dowb