Excel macro to filer by date and find dupes

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
i need a macro to be created in excel to first filter the data by latest or the last date in the end time column and delete rest of the data,then delete the ##### frm the result time of ( end time - start time) and then found the duplicates and paste in sheet 2 .

also i need to delete in dupes the one which has the higher time ---- this code i need separte.

Would be very thnkful to you. --- below is the data for your reference.
no start date start time type type number end date end time total time
6 2/16/2010 8:28:33 MTG MTG 6C36535 2/16/2010 9:06:56 38:23:00
6 2/16/2010 8:28:38 MTG MTG 6C36534 2/16/2010 9:07:09 38:31:00
6 2/16/2010 8:36:22 BND STP 5BGYFL8 2/16/2010 8:39:47 3:25
6 2/16/2010 9:12:11 BND STP 5BGYFP8 2/16/2010 9:14:13 2:02
6 2/16/2010 9:14:54 BND STP 5BGYFR6 2/16/2010 9:21:40 6:46
6 2/16/2010 9:20:16 BND GOV 5BGYFV4 2/16/2010 9:39:18 19:02
6 2/16/2010 9:34:09 BND COR 5BGYFV6 2/16/2010 9:39:50 5:41
6 2/16/2010 9:51:53 BND COR 5BGYFY5 2/16/2010 9:54:35 2:42
6 2/16/2010 10:00:40 BND MUN 3BFNHD1 2/16/2010 10:00:41 0:01
6 2/16/2010 10:38:31 BND GOV 5BGYGD4 2/16/2010 10:42:16 3:45
6 2/16/2010 11:22:27 BND MUN 3BFNKH5 2/16/2010 11:22:28 0:01
6 2/16/2010 11:22:43 BND MUN 3BFNKH6 2/16/2010 11:22:44 0:01
6 2/16/2010 11:22:59 BND MUN 3BFNKH7 2/16/2010 11:22:59 0:00
6 2/16/2010 11:25:48 BND MUN 3BFNKH9 2/16/2010 11:25:48 0:00
6 2/16/2010 11:27:49 BND COR 5BGYGL4 2/16/2010 11:27:49 0:00
6 2/16/2010 11:43:43 BND GOV 5BGYGP9 2/16/2010 11:51:59 8:16
6 2/16/2010 11:46:00 BND CMO 5F83783 8/17/2005 0:00:00 0.510416667
6 2/16/2010 11:46:26 BND MTG 1D04049 5/2/1991 0:00:00 12:14:34
6 2/16/2010 11:49:47 BND MUN 3BFNKJ1 2/16/2010 11:49:47 0:00
6 2/16/2010 11:51:48 BND COR 5BGYGP8 2/16/2010 11:51:48 0:00
6 2/16/2010 13:16:51 BND COR 5BGYGW5 2/16/2010 13:16:52 0:01
6 2/16/2010 14:15:48 BND MUN 3BFNKK8 2/16/2010 14:16:45 0:57
6 2/16/2010 14:19:40 BND COR 5BGYGZ9 2/16/2010 14:19:41 0:01
6 2/16/2010 14:33:12 BND MUN 3BFNKL4 2/16/2010 14:33:12 0:00
6 2/16/2010 14:42:31 BND COR 5BGYHB5 2/16/2010 14:42:32 0:01
6 2/16/2010 14:53:57 BND MUN 3BFNKL6 2/16/2010 14:53:57 0:00
6 2/16/2010 15:01:52 MTG MTG 6C32483 2/16/2010 14:15:37 1:13:45
6 2/16/2010 15:12:35 BND COR 5BGYHF7 2/16/2010 15:12:36 0:01
6 2/16/2010 15:30:35 BND GOV 5BGYHH3 2/16/2010 15:37:23 6:48
6 2/16/2010 15:33:56 BND MUN 3BFNKM2 2/16/2010 15:33:56 0:00
6 2/16/2010 15:35:57 BND COR 5BGYHH1 2/16/2010 15:35:57 0:00
6 2/16/2010 15:45:53 BND MUN 3BFNKM4 2/16/2010 15:45:53 0:00
6 2/16/2010 16:01:16 BND MUN 3BFNKM8 2/16/2010 16:01:16 0:00
6 2/16/2010 16:18:13 BND MUN 3BFNKP0 2/16/2010 16:18:14 0:01
6 2/16/2010 16:22:21 BND MUN 3BFNKP4 2/16/2010 16:22:22 0:01
6 2/16/2010 16:49:51 BND MUN 3BFNKP8 2/16/2010 16:49:51 0:00
6 2/16/2010 16:55:02 BND COR 5BGYHL9 2/16/2010 17:00:34 5:32
10 2/16/2010 9:35:22 BND STP 2331356 2/16/2010 9:44:06 8:44
10 2/16/2010 9:57:11 BND MTG 6C19353 2/16/2010 10:08:08 10:57
10 2/16/2010 10:14:25 MUN MUN 3BBYPB5 5/14/2007 12:04:31 25:51:06
10 2/16/2010 10:35:41 BND MTG 6404632 2/16/2010 10:46:41 11:00
10 2/16/2010 10:54:04 BND MTG 6C31961 2/16/2010 11:01:27 7:23
10 2/16/2010 10:57:59 BND MTG 6C31971 2/16/2010 11:05:51 7:52
10 2/16/2010 11:25:56 BND MTG 6C30995 2/16/2010 11:33:29 7:33
10 2/16/2010 11:25:57 BND MTG 6C37028 2/16/2010 11:34:11 8:14
10 2/16/2010 11:26:55 BND GOV 5BGYGP0 2/16/2010 11:44:22 17:27
10 2/16/2010 12:18:27 BND GOV 5BGYGR9 2/16/2010 12:21:09 2:42
10 2/16/2010 12:23:33 BND COR 5BGYGS7 2/16/2010 12:29:09 5:36
10 2/16/2010 12:26:04 BND MTG 6C32407 2/16/2010 12:26:40 0:36
10 2/16/2010 12:31:57 BND MTG 1AD2348 2/16/2010 13:10:56 38:59:00
10 2/16/2010 12:34:10 BND MTG 6404639 2/16/2010 12:35:24 1:14
10 2/16/2010 12:34:10 BND MTG 6404640 2/16/2010 12:37:14 3:04
10 2/16/2010 12:40:03 BND MTG 6C18516 2/16/2010 12:46:06 6:03
10 2/16/2010 12:42:16 BND MTG 6C18519 2/16/2010 12:46:39 4:23
10 2/16/2010 13:34:16 BND MTG 6C31032 2/16/2010 13:35:33 1:17
10 2/16/2010 14:06:23 BND MTG 6C32478 2/16/2010 14:11:07 4:44
10 2/16/2010 14:06:23 BND MTG 6C32479 2/16/2010 14:10:08 3:45
10 2/16/2010 14:06:24 BND MTG 6C32486 2/16/2010 14:14:24 8:00
10 2/16/2010 14:08:07 BND MTG 6C32480 2/16/2010 14:14:34 6:27
10 2/16/2010 14:10:20 BND MTG 6C32483 2/16/2010 14:15:37 5:17
10 2/16/2010 14:14:17 BND MTG 6C35355 2/16/2010 14:16:12 1:55
10 2/16/2010 14:28:16 BND MTG 6C16045 2/16/2010 14:32:11 3:55
10 2/16/2010 14:38:21 BND MTG 6C27402 2/16/2010 14:43:55 5:34
10 2/16/2010 14:44:59 BND GOV 5BGYHC6 2/16/2010 14:52:01 7:02
10 2/16/2010 15:43:49 BND GOV 5BGYHH7 2/16/2010 15:49:04 5:15
10 2/16/2010 16:01:44 BND COR 5BGYHM2 2/16/2010 17:16:28 1:14:44
10 2/16/2010 16:21:51 BND GOV 5BGYHL6 2/16/2010 16:43:05 21:14
18 2/16/2010 9:50:19 BND MUN 3BFNHC8 2/16/2010 9:50:20 0:01
18 2/16/2010 11:40:39 BND GOV 5BGYGV0 2/16/2010 12:51:33 1:10:54
18 2/16/2010 11:41:09 BND GOV 5BGYGV1 2/16/2010 12:51:48 1:10:39
18 2/16/2010 12:13:17 BND MUN 3BFNKJ9 2/16/2010 12:13:17 0:00
18 2/16/2010 12:44:40 BND STP 5BGYGT9 2/16/2010 12:50:17 5:37
18 2/16/2010 12:56:56 BND MUN 3BFNKK4 2/16/2010 12:56:57 0:01
18 2/16/2010 14:42:07 BND COR 5BGYHC7 2/16/2010 14:53:16 11:09
18 2/16/2010 15:13:30 BND GOV 5BGYHG2 2/16/2010 15:17:29 3:59
18 2/16/2010 15:43:25 BND MUN 3BFNKM3 2/16/2010 15:43:25 0:00
18 2/16/2010 15:57:53 BND MUN 3BFNKM7 2/16/2010 15:57:54 0:01
18 2/16/2010 16:19:57 BND MUN 3BFNKP1 2/16/2010 16:19:58 0:01
18 2/16/2010 17:03:51 BND MUN 3BFNKP9 2/16/2010 17:03:51 0:00
18 2/16/2010 17:06:48 BND MUN 3BFNKQ2 2/16/2010 17:06:48 0:00

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Based on your data, the max end time is 17:16:02 and that is the only row with that value. Are you sure you want to delete all rows that are not equal to that value. Also what ##### ? I dont see any. In Excel ##### is basicially showing that column width is too small to show you the number, Also what do you mean by duplication.Is is some column where the values cannot be duplication or you are saying the whole row needs to be identical