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.

Row by Row Comparison & Deletion
 

Hi MArk,

Am Mon, 11 May 2020 15:14:27 -0700 (PDT) schrieb NoodNutt:

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

try:

Sub Test()
Dim LRow As Long, i As Long
Dim First As Long, Last As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LRow To 5 Step -1
First = i
Last = Application.CountIf(.Range("B4:B" & LRow), .Cells(First, 2))
If Last 1 Then
.Rows(First & ":" & First - Last + 2).Delete
i = i - Last + 1
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Row by Row Comparison & Deletion
 

Hi Claus

Thank you for your prompt response.

I thought it easier to send you a test file direct so you can see first hand.

I will keep the group updated as to the outcome, when available.

As always
Thank you for your assistance.
Mark.

Row by Row Comparison & Deletion
 

Hi Mark,

Am Mon, 11 May 2020 16:38:35 -0700 (PDT) schrieb NoodNutt:

I thought it easier to send you a test file direct so you can see first hand.

I will keep the group updated as to the outcome, when available.

sorry, I didn't read your question with care

Try:

Sub Test()
Dim LRow As Long, i As Long
Dim First As Long, Last As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LRow To 4 Step -1
First = i
Last = Application.CountIfs(.Range("B3:B" & LRow), .Cells(First, "B"), _
.Range("A3:A" & LRow), .Cells(First, "A"))
If Last 1 Then
.Cells(First, "C") = .Cells(First, "C").Offset(-Last + 1)
.Rows(First - 1 & ":" & First - Last + 1).Delete
i = i - Last + 1
End If
Next
End With
End Sub

Regards
Claus B.
--
Windows10
Office 2016

Row by Row Comparison & Deletion
 

Hi Claus

I have said it before, and will continue to say it! You are the Mailman because you literally keep delivering the goods.

Works perfectly.

Thank you so much.
Warm regards
Mark.

Row by Row Comparison & Deletion
 

On Tuesday, May 12, 2020 at 4:18:50 PM UTC-4, NoodNutt wrote:
Hi Claus

I have said it before, and will continue to say it! You are the Mailman because you literally keep delivering the goods.

Works perfectly.

Thank you so much.
Warm regards
Mark.

The Mailman, or perhaps just a skinnier version of Santa-'Claus'! Either way, he is always there with thoughtful, deliberate, delivery!