Duplicate Based on Multi-Coulmn - Only Keep the Earliest and Latest Value

[jturner12]

Below is an example of an Excel sheet I'm working with:

Excel-Duplicates.png
http://i.stack.imgur.com/KKHkF.png

Basically, I'm trying to delete the duplicate rows by matching ID, Date and Type. If ID, Date and Type are the same, then, I want to only keep the record with the earliest Time in case of Type = In and the latest Time in case of Type = Out.

So, for example, in the case of ID = 1, there are 3 records for In, I only want to keep the one where Time is: 8:01 as this is the earliest. The other 2 records should be deleted.

Similarly, in the case of ID 3, I want to keep the record where Time = 18:05 as this is the later time out of the 2.

Can this be achieved by Conditional Formatting or Macro or VBA?

Many thanks for your help in advance.

[Kaper]

1) by conditional formatting you wil not delete any dupes you can only highlight them.
2) macro could of course do this.
3) one can probably add a column(s) with formula and then autofilter or delete duplicates on this
4) one picture is worth 100 words, but on this forum one workbook is worth 100 pictures - attach a sample workbook (see in forum rules how).

simple sample ;-) solution (based on p.3 above):
in I2 formula: =A2&E2
in J2 formula: =IF(E2="Out",1-D2,D2)
(you can format output as time if you wish) and copy botch cells down
Select all data and these 2 extara columns and sort data (use sort dialog) on these 2 columns (both ascending)
From Data ribbon -> Remove Duplicates (using only column I)
delete helper columns.

[jturner12]

hi kaper,

Many thanks for your reply. I have attached a sample Spreadsheet. I will try your suggestion.

If there are any more suggestions for achieving this I would be grateful.

Thank You once again

Duplicates_Time.xls

[jindon]

Try this

Please Login or Register  to view this content.

[jturner12]

Many Thanks Jindon, that seems to be working perfectly

[jindon]

You are welcome and thanks for the rep.

It will be easier and shorter, if the data always sorted by the time.

I considered when it is not sorted so a little complex....