Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    Uranium Lounger
    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

  4. #4
    2 Star Lounger
    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?

  5. #5
    Uranium Lounger
    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

  6. #6
    2 Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •