group items and divide by coloured row

kendals

Board Regular
Joined
May 19, 2007
Messages
55
Hi,

I need to group items by date and time and for each group to be separated by a coloured row. So for example september 3rd at 2am may contain rows of info, after that would be a coloured row.

how do i do this?

thanks
Kunal
 
Hi again Kunal,

Yes, but the example you posted indicates that your dates are just text strings, not real Excel dates. If you change them to real dates I believe my macro will work for you. If you cannot change them, I could change my code so that it would work with the text string dates. But if there is no reason why you must have text strings, I recommend changing them to Excel dates.

Damon
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
data

Hi,

The export from the helpdesk software is a csv with the dates as text.
There can be about 1000-8000 items in this list. So the if the dates were text that would be better

Thanks
 
Upvote 0
Kunal,

Here is a macro you can use to convert all dates in a range of cells to valid Excel dates:

Code:
Sub Change2Dates()
   'changes text string dates in the format "2/12/2006  6.53" to true Excel dates
   'process all cells in the selected range
   Dim Cell       As Range
   Dim strDate    As String
   For Each Cell In Selection
   If Not IsEmpty(Cell) Then
      strDate = Replace(Cell.Text, ".", ":")
      If IsDate(strDate) Then Cell.Value = CDate(strDate)
   End If
   Next Cell
End Sub

Simply select the entire worksheet column containing the dates, then run this macro.

If this works for you, you can incorporate this code directly into the code I previously provided to convert to do the date conversion prior to doing the sort. To do this the new code would be

Code:
Sub GroupByDate() 

   'Sorts dates, then looks for date changes in column A and inserts 
   'blank color-filled row (cols A and B only) when date changes 
 
  'changes text string dates in the format "2/12/2006  6.53" to true Excel dates
   'process all cells in column A
   Dim Cell       As Range
   Dim strDate    As String
   For Each Cell In Range("A:A")
   If Not IsEmpty(Cell) Then
      strDate = Replace(Cell.Text, ".", ":")
      If IsDate(strDate) Then Cell.Value = CDate(strDate)
   End If
   Next Cell

   Dim iRow       As Long 
   Dim LastCell   As Range 
   Dim DT         As Date 
    
   'Sort columns A and B using col A (dates) as the key. 
   Set LastCell = Range("B65536").End(xlUp) 
   Range("A2", LastCell).Sort key1:=Range("A1") 
    
   'Now insert grouping separators 
   iRow = 2 
   Do 
      If Int(Cells(iRow + 1, "A")) > Int(Cells(iRow, "A")) Then 
         'date change occurred between iRow and iRow+1 
         iRow = iRow + 1 
         Rows(iRow).Insert 
         Range(Cells(iRow, "A"), Cells(iRow, "B")).Interior.Color = RGB(100, 100, 100) 
      End If 
      iRow = iRow + 1 
   Loop Until IsEmpty(Cells(iRow, "A")) 
    
End Sub

Damon
 
Upvote 0
thanks

Hi Damon thanks for your help, i have it working now.
There is a slight change now to the code, after each hour there needs to be a colored row as well. Could you help me?
 
Upvote 0
Yes, here is a version that inserts both hour and date change colored rows. Note that the coloring is different for hours than for days.

Code:
Sub GroupByDate()
   'Sorts dates, then looks for date changes in column A and inserts
   'blank color-filled row (cols A and B only) when date changes
   Dim iRow       As Long
   Dim LastCell   As Range
   Dim DT         As Date
   
   'Sort columns A and B using col A (dates) as the key.
   Set LastCell = Range("B65536").End(xlUp)
   Range("A2", LastCell).Sort key1:=Range("A1")
   
   'Now insert grouping separators
   iRow = 2
   Do
      If Int(Cells(iRow + 1, "A")) > Int(Cells(iRow, "A")) Then
         'date change occurred between iRow and iRow+1
         iRow = iRow + 1
         Rows(iRow).Insert
         Range(Cells(iRow, "A"), Cells(iRow, "B")).Interior.Color = RGB(100, 100, 100)
      ElseIf Int(Cells(iRow + 1, "A") * 24) > Int(Cells(iRow, "A") * 24) Then
         'hour change occurred between iRow and iRow+1
         iRow = iRow + 1
         Rows(iRow).Insert
         Range(Cells(iRow, "A"), Cells(iRow, "B")).Interior.Color = RGB(255, 255, 50)
      End If
      iRow = iRow + 1
   Loop Until IsEmpty(Cells(iRow, "A"))
   
End Sub


Damon
 
Upvote 0
thanks

hi damon,
thank you.
Have you done this kind of script before? its quite cool the way it works.
 
Upvote 0
Hi again kendals,

I have done similiar scripts before, i.e., inserting lines on various conditions occurring. After one has done a few thousand scripts it starts to get easier. :wink:

I'm glad we finally got to the point where it does what you want.

Damon
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top