Delete cell in a column and adjacent cell if first cell has same beginning values

[Ed Marvel]

I am a newbie in need of some help.

I may not have explained clearly in title or tags. Suggestions welcome on that as well.

I have two columns of data that can have up to 50 or more entries.
The first column contains a date/time in the example format "10/1/17 11:59:52 PM CDT"
The adjacent cell contains the data for that corresponding date/time stamp.
I need to delete the date cell A and adjacent data in B for any cell in A with the same date leaving the latest timestamp and data.

This is a sample of my problem...

A B
10/1/17 11:59:43 PM CDT 1462.5
10/2/17 11:59:14 PM CDT 1498.7
10/3/17 11:58:52 PM CDT 1845.5
10/3/17 11:59:24 PM CDT 1846.8
10/3/17 11:59:14 PM CDT 1847.6
10/4/17 11:59:37 PM CDT 1502.5

I need to delete/shift lower cells up, the data in A1 and A2 of the earlier times of the same date (highlighted red)leaving only the last entry for that date.

At this stage the data in A is formatted as text because of the trailing CDT.

I'm thinking must be done in VBA but have no idea of the syntax required.
I can send my sheets to see how i have gotten all other formatting if you don't laugh.

Any help is greatly appreciated

 

[igold]

Hi Ed,

It seems like your data is not correct as it shows you wanting to delete a row in red which is a later timestamp than the value below.

10/3/17 11:59:24 PM CDT 1846.8
10/3/17 11:59:14 PM CDT 1847.6

 

[JLGWhiz]

Assuming your actual data is in ascending order from top to bottom. Try this on a copy of your data before applying it to the original.

Sub t()
Dim lr As Long, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 2 Step -1
        If Left(Cells(i, 1), InStr(Cells(i, 1).Value, " ") - 1) = _
           Left(Cells(i - 1, 1), InStr(Cells(i, 1).Value, " ") - 1) Then
            Cells(i - 1, 1).Resize(1, 2).Delete xlShiftUp
        End If
    Next
End Sub

 

[Ed Marvel]

Ooops. Your right. I was entering it in manually on the forum.
They are in descending order, oldest to newest.

Thanks for the catch

 

[Ed Marvel]

Wow!

Thank you so much JLGWhiz

It worked perfectly

I really appreciate your help on this.

 

[Ed Marvel]

Solved!!!

Thank you again for the VBA magic JLGWhiz

 

[Ed Marvel]

JLGWhiz

Sorry I didn't mention all.
I have 3 data sets.
They are the same type of data as my earlier example but there two more sets different columns.

The first set A and B. Second set D and E. Third Set G and H

I hope I didn't waste your time earlier. I've been trying to figure out how to do this on my own all night.

Thank you

e

 

[JLGWhiz]

Wow!

Thank you so much JLGWhiz

It worked perfectly

I really appreciate your help on this.

You're welcome,
regards, JLG

 

[JLGWhiz]

JLGWhiz

Sorry I didn't mention all.
I have 3 data sets.
They are the same type of data as my earlier example but there two more sets different columns.

The first set A and B. Second set D and E. Third Set G and H

I hope I didn't waste your time earlier. I've been trying to figure out how to do this on my own all night.

Thank you

e

Sub t()
Dim lr As Long, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
For c = 1 To 7 Step 3
    For i = lr To 2 Step -1
        If Left(Cells(i, c), InStr(Cells(i, c).Value, " ") - 1) = _
           Left(Cells(i - 1, c), InStr(Cells(i, c).Value, " ") - 1) Then
            Cells(i - 1, c).Resize(1, 2).Delete xlShiftUp
        End If
    Next
Next
End Sub