基于Multi-Coulmn复制 - 仅保留最早和最新价值

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

下面是我正在使用的Excel工作表的示例:

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.

基本上,我试图通过匹配ID,日期和类型来删除重复的行。如果ID,日期和类型相同,那么,我想仅在Type = In的情况下保留记录最早的时间,在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.

因此,例如,在ID = 1的情况下,In有3条记录,我只想保留Time为8:01的记录,因为这是最早的。应删除其他2条记录。

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.

类似地,在ID 3的情况下,我想保留时间= 18:05的记录,因为这是2的较晚时间。

Can this be achieved by Conditional Formatting or is it more complex than that?

这可以通过条件格式实现还是比它更复杂?

Many thanks for your help in advance.

非常感谢您的帮助。

1 个解决方案

#1

Conditional Formatting does not to me look the best approach. Depending upon how often required (frequently, and VBA may be better) I suggest:

条件格式不是我看起来最好的方法。根据需要的频率(经常和VBA可能更好),我建议:

1. Add a sequential index (so can be sorted back to original order, if required).

添加顺序索引(因此可以根据需要排序回原始顺序)。

2. Sort on Time (Smallest to Largest) within Type.

按类型排序时间(最小到最大)。

3. Select Type Out and sort Largest to Smallest.

选择“类型输出”并将“最大”排序为“最小”。

4. Select all, in Remove Duplicates uncheck all but Name, Date and Type and OK.

选择all,在Remove Duplicates中取消选中除Name,Date和Type以外的所有内容,然后单击OK。

5. Resort and delete index, if desired.

度假和删除索引,如果需要。