Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidate data that occurs close together in time
I have about 7 columns of data. The second one has about 150 different types
of components, about 2000 entries in all. the third column is the start date im looking for, the fourth is the start time. Fifth is the end date, Sixth is the end time. I am looking for a macro to go through each component, check the next component to see if its the same one, if it is, check the end date/time of the first one, and if it is within one day of the start time of the next entry, combine the two entries and delete the old ones. What I have so far is (This is kinda long, I apologize) Sub Compare_Dates() Dim CompRange As Range, CopyRange As Range Dim There As Boolean, This As Boolean Dim days As Date Dim Hours As Double There = False LastRow1 = Sheets("Sheet1").Cells(Cells.Rows.Count, "A").End(xlUp).Row Set CompRange = Sheets("Sheet1").Range("A2:A" & LastRow1) For Each c In CompRange If c.Value = c.Offset(1, 0).Value Then There = True End If If There Then days = c.Offset(0, 4).Value - c.Offset(1, 2).Value Hours = c.Offset(0, 5).Value - c.Offset(1, 3).Value If days + Hours <= 1 Then c.Offset(1, 2) = c.Offset(0, 2).Value c.Offset(1, 3) = c.Offset(0, 5).Value 'Need to check the date of c.offset(0,4) with c.offset(1,2) 'If they are within x days of each other 'c.offset(1,2).value=c.offset(0,2).value Else There = False If There Then If CopyRange Is Nothing Then Set CopyRange = c.Offset(1, 0).EntireRow Else Set CopyRange = Union(CopyRange, c.Offset(1, 0).EntireRow) End If End If 'This If loop checks if the CopyRange currently has any rows 'If not, it places c in it End If End If There = False Next If Not CopyRange Is Nothing Then CopyRange.Delete End If End Sub I think the only problem is comparing the dates, Other than that I feel like it should work. Any help with this macro, or any ideas for a different macro would be greatly appreciated! Thanks in advance, sorry for the long post!! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a Value the first Time It Occurs | Excel Worksheet Functions | |||
Weekly Time Sheet Consolidate | Excel Worksheet Functions | |||
CountIF columnH2-H101 the number of time between ages 20-29 occurs | Excel Worksheet Functions | |||
formula to extract specific data if match occurs | Excel Worksheet Functions | |||
Can I have a loop to open a set of workbooks get some data, close it one a time. | Excel Programming |