Duplicates-Replace adjacent cell with lowest value

[pizzaguy4487]

Hello All!

Example workbook attached.

I need to write a vba script that finds the duplicates in column A, then copies the lowest value in column E to each duplicate.

For example, there are two of Brandy but with two different dates in E (8/21/2013 and 8/21/2010). I need E in each row that has Brandy in column A to show 8/21/2010. Same scenario with CJ, but CJ needs to show 1/6/2010 in each row.

Any thoughts on how to do this?

Thanks a bunch!

[Trebor76]

Hi there,

Try this (though initially on a copy of your data as the results cannot be undone of they're not as expected):

Please Login or Register  to view this content.

Regards,

Robert

[sintek]

Add Headings Row for another option...

Please Login or Register  to view this content.

[jindon]

much easier...
For 2019, 365

Please Login or Register  to view this content.

Or if 2016 or earlier, then

Please Login or Register  to view this content.

[sintek]

@ jindon

Think Op wants...

finds the duplicates in column A, then copies the lowest value in column E to each duplicate.

Lowest date of found duplicate Column E of each duplicate...

[jindon]

Updated my code in post #4.

[sintek]

Will MINIFS work in OP version...
Does not work with my 2010,2013,2016

[pizzaguy4487]

Hi there,

Try this (though initially on a copy of your data as the results cannot be undone of they're not as expected):

Please Login or Register  to view this content.

Regards,

Robert

This worked perfectly! Thank you!

Add Headings Row for another option...

Please Login or Register  to view this content.

Appreciate the reply, I did not try your code because the data set I am working with doesnt have headers. Yes it would be easy enough to add them either manually or vba but for the sake of not adding anything extra...

much easier...
For 2019, 365

Please Login or Register  to view this content.

Or if 2016 or earlier, then

Please Login or Register  to view this content.

Your code for 2019 (of which I have) worked perfectly as well! Lot less to type thats for sure! Thank you!

Will MINIFS work in OP version...
Does not work with my 2010,2013,2016

Not sure how it would work on other versions. I do need to make this compatible with earlier versions though, so I might have a backup copy with Trebor76's code.

Just some back story on this project, I have 11 retail locations and pay vacation time to all my employees after one year of service regardless of if they are part time or full time. The vacation time I pay is based upon average hours per week worked in the last six months. Some of my employees work in multiple locations all the time, some volunteer to help out at another locations for a week or two or three if need be. I was able to write code that removes duplicates and adds up hours from all locations for each employee. Problem was when I applied the filter for one year of service. Each location's "start" date doesn't sync across the master data file, so if in the last 3 months an employee pulled doubles to help out a different location, their "start" date would have kicked them out of a certain location when it comes to the calculations.

This code inserted before the already written code will give an accurate average for all.

Thank you all so much for your time and assistance!

EDIT: I know my code is dirty, probably really dirty..it was a hack job mash up. Being an eleven store pizza guy and trying to learn vba, wheres the time.... I've made a few scripts that really make my life a lot easier, one click rather than twenty minutes. Wish I could learn more about how each one works though. If any of you would be willing to explain line by line what is happening in the above I would be very grateful. Not a request by any means, but if you feel like it, I am all ears Otherwise i will google everything above and try to make sense of it all.