View Single Post
  #1   Report Post  
Old May 11th 20, 11:14 PM posted to microsoft.public.excel.programming
NoodNutt[_2_] NoodNutt[_2_] is offline
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 39
Default Row by Row Comparison & Deletion

Hi Team

Was wondering if the following is possible please.


StoreName as Range. = Col_A
TruckNo as Range... = Col_B
TimeArr..... = Col_C (dd/mm/yyyy h:mm)
TimeDep..... = Col_D (dd/mm/yyyy h:mm)

1. In the example below, the first TruckValue is "Truck 03"
2. I need to Find TruckValue.firstRow & TruckValue.lastRow of this TruckValue
3. All rows in between need to be deleted
4. TruckValue.FirstRow.Offset(,2).value = TruckValue.LastRow.Offset(,2)
5. Delete TruckValue.LastRow
6. Next

If there is only one Row of TruckValue Data then skip to next TruckValue

Col_A...........Col_B...........Col_C............. ......Col_D
.................................Arrive........... .......Depart
Site...........Vehicle..........Time.............. ......Time
Store 1 Truck 03 08/05/2020 08:20:45 08/05/2020 09:41:53
Store 1 Truck 03 08/05/2020 09:42:05 08/05/2020 09:42:27
Store 1 Truck 03 08/05/2020 09:42:38 08/05/2020 09:43:14
Store 1 Truck 03 08/05/2020 09:43:22 08/05/2020 09:43:42
Store 1 Truck 03 08/05/2020 09:44:04 08/05/2020 09:44:26
Store 1 Truck 03 08/05/2020 09:44:28 08/05/2020 10:11:12
Store 2 Truck 04.1 08/05/2020 08:32:20 08/05/2020 09:15:19
Store 2 Truck 04.2 08/05/2020 10:58:10 08/05/2020 11:34:02
Store 2 Truck 04.2 08/05/2020 11:34:08 08/05/2020 11:56:56
Store 2 Truck 04.2 08/05/2020 11:57:13 08/05/2020 12:11:14
Store 2 Truck 04.2 08/05/2020 12:12:50 08/05/2020 12:14:34
Store 2 Truck 04.2 08/05/2020 12:15:16 08/05/2020 12:15:25
Store 2 Truck 04.2 08/05/2020 12:16:47 08/05/2020 12:20:31
Store 2 Truck 05 08/05/2020 08:24:39 08/05/2020 08:32:00
Store 2 Truck 05 08/05/2020 08:32:39 08/05/2020 08:40:41
Store 2 Truck 05 08/05/2020 08:41:21 08/05/2020 09:03:39
Store 2 Truck 05 08/05/2020 09:05:23 08/05/2020 09:05:26
Store 2 Truck 05 08/05/2020 09:05:40 08/05/2020 09:05:48
Store 2 Truck 05 08/05/2020 09:07:30 08/05/2020 09:08:04
Store 2 Truck 05 08/05/2020 09:08:09 08/05/2020 09:08:12
Store 2 Truck 05 08/05/2020 09:08:15 08/05/2020 09:08:18
Store 2 Truck 05 08/05/2020 09:08:51 08/05/2020 09:23:38
Store 3 Truck 06 08/05/2020 09:56:15 08/05/2020 09:56:24
Store 3 Truck 06 08/05/2020 09:56:52 08/05/2020 09:56:58
Store 3 Truck 06 08/05/2020 10:02:56 08/05/2020 10:03:14
Store 3 Truck 06 08/05/2020 10:08:10 08/05/2020 10:08:16
Store 3 Truck 06 08/05/2020 10:08:33 08/05/2020 10:09:01
Store 4 Truck 01 08/05/2020 05:54:30 08/05/2020 07:09:39
Store 5 Truck 07 08/05/2020 10:36:05 08/05/2020 11:47:25
Store 5 Truck 08 08/05/2020 11:22:48 08/05/2020 12:24:16
Store 5 Truck 09 08/05/2020 08:24:43 08/05/2020 09:28:21
Store 5 Truck 12 08/05/2020 09:07:40 08/05/2020 10:43:11
Store 5 Truck 14 08/05/2020 11:06:55 08/05/2020 11:58:29

Hopefully, the result should look like this:

Col_A...........Col_B...........Col_C............. ......Col_D
.................................Arrive........... .......Depart
Site...........Vehicle..........Time.............. ......Time
Store 1 Truck 03 08/05/2020 08:20:45 08/05/2020 10:11:12
Store 2 Truck 04.1 08/05/2020 08:32:20 08/05/2020 09:15:19
Store 2 Truck 04.2 08/05/2020 10:58:10 08/05/2020 12:20:31
Store 2 Truck 05 08/05/2020 08:24:39 08/05/2020 09:23:38
Store 3 Truck 06 08/05/2020 09:56:15 08/05/2020 10:09:01
Store 4 Truck 01 08/05/2020 05:54:30 08/05/2020 07:09:39
Store 5 Truck 07 08/05/2020 10:36:05 08/05/2020 11:47:25
Store 5 Truck 08 08/05/2020 11:22:48 08/05/2020 12:24:16
Store 5 Truck 09 08/05/2020 08:24:43 08/05/2020 09:28:21
Store 5 Truck 12 08/05/2020 09:07:40 08/05/2020 10:43:11
Store 5 Truck 14 08/05/2020 11:06:55 08/05/2020 11:58:29

As always
Many thanks in advance for any assistance.

Cheers
Mark.