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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi Kunal,

Probably the easiest way would be to write a simple macro to insert and color the "separator rows". Are your date-time values in a particular column? And do you want the separators inserted each day, each 8 hours, or what?

Damon
 
Upvote 0
more info

well, the thing is the data is random, so it will need to detect the end of a group then put a separator in. See the data is based upon how many problems happen each day. So you could have 10 that have happened at 10am on the 5th and 30 that have happened yesterday. Each one will need to be sorted by date and between each group will be a colored row
 
Upvote 0
Hi again kendals,

I'm still not clear as to what constitutes a "group" and how the algorithm should determine where one group ends and the next one starts. Do the two sets of problems you referred to constitute two groups because they occurred on different dates? Can you have multiple groups in a given day? Can a "problem" span a date boundary?

Damon
 
Upvote 0
hi

ok, heres what happens. I have a list of data that lists all network problems with the date and time that is happened.
The code needs to sort the list in order to group them, after each group separate with a blank row.

So maybe, Count unique items if same date and time equals true sort by name and insert row after last item in list.
 
Upvote 0
items

each item is grouped by day. A problem can exist on multiple days but will show up as individual items. On the right we have the normal data on the left is how i want it to look.

2003074101878289192_rs.jpg
 
Upvote 0
Hi again kendals.

Okay, now I have something to work with. Give this macro a try.

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)
      End If
      iRow = iRow + 1
   Loop Until IsEmpty(Cells(iRow, "A"))
   
End Sub

This macro operates on the currently active worksheet.

To install this macro, go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM), and paste this code into the Code pane. To run the macro go back to Excel and Alt-TMM.

Good luck.

Damon
 
Upvote 0
errors

Hi thanks for your post, i now get type mismatch error 13 on line

If Int(Cells(iRow + 1, "A")) > Int(Cells(iRow, "A")) Then
 
Upvote 0
Kunal,

I believe the problem could be that your dates are not true Excel date values, but rather text strings. If they were dates I would expect them to be right-justified in the cells (unless you deliberately left-justified them), which does not appear to be the case. My code assumes they are Excel date values.

If they are text strings, not dates, is there a reason why you don't want them as true Excel dates? If not, I suggest you convert them. I'm not sure of the correct date format in your locale, but in mine it would be necessary to replace all the "." hh.mm separators with colons, so that the times would look like "2/12/06 2:53".

Damon
 
Upvote 0
errors

hi i am still getting the same error, i have used the example that i posted but still get the same problem
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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