Sorting data – most current date of duplicate record

[Ron512]

Your help with sorting and identifying data in a workbook would be very much appreciated.

I have a excel workbook that has a part number in column H and a delivery date in column K. There are multiple records (rows) with the same part number and potentially different delivery dates. I would like to identify the record for each part number that has the most current delivery date and highlight the row.

There are about 6000 rows of data and several different part numbers.

I am comfortable working with a VBA macros although if there is a formula that will work that is ok as well.

Thanks
Ron

 

[Marcelo Branco]

You can achieve this with Conditional Formatting

Select H2:K6000 being H2 the active cell (the one not shaded after the selection)

Home > Conditional Formatting > New Rule > Use a formula to determine...

Insert this formula
=AND($H2<>"",$K2=MAX(IF($H$2:$H$6000=$H2,$K$2:$K$6000)))
pick the format you want

Hope this helps

M.

 

[Ron512]

Marcelo,
Works great thanks!

The user now wants all of the rows not identified, (highlighted), deleted.

Suggestions?

Thanks
Ron

 

[Marcelo Branco]

You need a macro

Or (without macro)

I would use a helper column say column Z and put in Z1 the header
Helper

In Z2 copied down till the end of your data this array formula
=--AND($H2<>"",$K2<>MAX(IF($H$2:$H$6000=$H2,$K$2:$K$6000)))
confirmed with Ctrl+Shift+Enter

Then you can filter column Z (criteria = 1) and delete the visible rows

M.

 

[Ron512]

Marcelo,

I understand your method although I came up with another solution prior to your post.
I created a excel table so I could filter by color and just copied visible cells to another sheet.

Thanks for your help!

Ron

 

[Marcelo Branco]

Marcelo,

I understand your method although I came up with another solution prior to your post.
I created a excel table so I could filter by color and just copied visible cells to another sheet.

Thanks for your help!

Ron

Nicely done!

M.