Results 1 to 6 of 6

20060209, 20:41 #1
 Join Date
 May 2005
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Data anaylisis Formula (Excel 2003)
Was wondering if there is a bright idea to help me figure out my data,
I was wondering how to do a macro which would highlight the first new day of the data font Red.
then
I have 2 columns and 2 blank on in the middle of the two.
in one I have a unique number and in the other I have a date. I want to find a simple way of look at the unique number for each day, if the unique number is consective with the one below it for the same day then 1 group, would show up in the middle column (col. [img]/forums/images/smilies/cool.gif[/img] on the row beside the last consective number. Attached is my example with my attempt on the first few.
then
in col, D get the number of unique Numbers for that day.
Thanks for reading

20060209, 20:57 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Data anaylisis Formula (Excel 2003)
> I was wondering how to do a macro which would highlight the first new day of the data font Red.
You don't need a macro for that, you can use conditional formatting:
 Select A2:C413 (or a larger area).
 Select Format  Conditional Formatting...
 Select Formula Is in the first dropdown list.
 Enter <code>=$C2>$C1</code> in the box next to it. The placement of the $ signs is important.
 Click Format...
 Specify the font and other formatting you want to apply.
 Click OK twice.

20060209, 21:03 #3
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Data anaylisis Formula (Excel 2003)
The attached file uses conditional formatting to set the row text color to red for the first row of each new date.
I do not see a consistent pattern for the Group labels in column B. What is in the sheet does not seem to match what you describe in your post.Legare Coleman

20060209, 22:16 #4
 Join Date
 May 2005
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Data anaylisis Formula (Excel 2003)
Sorry I think Imiss read the question you posted.
I am trying to count how many jobs have been done each day. each number in col A is 1 job for example
in col a the unique numbers,
col b
the dates
col c the number of jobs done that day
is there a way to count the number of Unique numbers for that day?

20060210, 02:07 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Data anaylisis Formula (Excel 2003)
OK, now I see what you are trying to do. Here is a macro that will do what I think you want in columns B, D, and E.
<code>
Public Sub SetGroups()
Dim lLastRow As Long, I As Long, lGCnt As Long, lJcnt As Long
With Worksheets("Crew working")
lLastRow = .Range("A65536").End(xlUp).Row  1
I = 1
lGCnt = 1
lJcnt = 1
Do While I <= lLastRow
If .Range("C1").Offset(I, 0).Value <> .Range("C1").Offset(I + 1, 0).Value Then
.Range("B1").Offset(I, 0).Value = "Group " & lGCnt
.Range("D1").Offset(I, 0).Value = lJcnt
.Range("E1").Offset(I, 0).Value = lGCnt
lGCnt = 1
lJcnt = 1
Else
If CLng(.Range("A1").Offset(I, 0).Value) + 1 <> CLng(.Range("A1").Offset(I + 1, 0).Value) Then
.Range("B1").Offset(I, 0).Value = "Group " & lGCnt
lGCnt = lGCnt + 1
End If
lJcnt = lJcnt + 1
End If
I = I + 1
If I < lLastRow Then
Do While .Range("A1").Offset(I, 0).Value = ""
I = I + 1
Loop
End If
Loop
End With
End Sub
</code>Legare Coleman

20060211, 01:25 #6
 Join Date
 May 2005
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Data anaylisis Formula (Excel 2003)
Thank You, Thanks for the big hand