count number of occurences on a particular date

[M.Speare]

Hello all:

I have a spreadsheet where I want to count the occurences of a value
on a certain date and then use the resulting information in a chart.

My spreadsheet looks someting like this:


Column *A* contains the ID of the defect
Column *B* contains the status
Column *E* contains the open date
Column *F* contains the closing date if closed.


An example spreadsheet would be:


123 Closed 05/12/2006 07/24/2006
100 Open 04/30/2006
125 Fixed 05/12/2006
180 New 09/22/2006
140 Closed 07/01/2006 07/24/2006


I would like to:


*a)* count the number of defects for each status on each date (answer
should be:

New Open Fixed Closed
1 1 1 2



Please help, as I have spent too much time on this already!

--

 

[jlepack]

I put your little table "New Open Fixed Closed" into cell F1 to I1 and
into F2 I put the formula:

=COUNTIF($B:$B,F1)

and then I filled it across to I2.

Cheers,
Jason Lepack

 

[M.Speare]

Sorry:

My brain must have been working faster than my fingers or vice versa
anyway, what I am looking for is actually this.


My spreadsheet looks someting like this:


Column *A* contains the ID of the defect
Column *B* contains the status
Column *E* contains the open date
Column *F* contains the closing date if closed.


An example spreadsheet would be:


123 Closed 05/12/2006 07/24/2006
100 Open 04/30/2006
125 Fixed 05/12/2006
180 New 09/22/2006
140 Closed 07/01/2006 07/24/2006


I would like to:


*a)* count the number of defects that are open on each date... New Open

and Fixed are considered Open defects (answer
should be:


Open Date
1 04/30/2006
1 05/12/2006
1 09/22/2006


*b)* count the number of closed defects for a particular date,


Closed Date
2 07/24/2006

Could someone help me please???

Thanks,
-M

 

[jlepack]

Sub OpenClosedTable()
Dim rM As Range, rC As Range, rO As Range, rT As Range
Dim wsOld As Worksheet, wsNew As Worksheet
Dim oCol As String, cCol As String

' Initialize
Set wsOld = Sheets("sheet1")

' Comment this if you want to use the new sheet
Set wsNew = Sheets("sheet1")
wsNew.Range("F:I").Delete
wsNew.Range("F1").Value = "Open"
wsNew.Range("G1").Value = "Date"
wsNew.Range("H1").Value = "Closed"
wsNew.Range("I1").Value = "Date"
'intialize the pointers
Set rC = wsNew.Range("H2") ' closed table
Set rO = wsNew.Range("F2") ' open table
Set rM = wsOld.Range("A1") ' main table
oCol = "G:G"
cCol = "I:I"
' End Here

' 'uncomment this if you want your reults in a new sheet
' Set wsNew = Sheets.Add
' wsNew.Range("A1").Value = "Open"
' wsNew.Range("B1").Value = "Date"
' wsNew.Range("C1").Value = "Closed"
' wsNew.Range("D1").Value = "Date"
' 'intialize the pointers
' Set rC = wsNew.Range("C2") ' closed table
' Set rO = wsNew.Range("A2") ' open table
' Set rM = wsOld.Range("A1") ' main table
' oCol = "B:B"
' cCol = "D"
' ' End Here

' format the dates
Set rT = wsNew.Range(oCol & "," & cCol)
rT.NumberFormat = "m/d/yyyy"

' start the job
Do While Not rM.Value = "" ' if the list isn't contiguous - problem
If rM.Offset(0, 1).Value = "Closed" Then
Set rT = wsNew.Range(cCol)
' search for the current date in the main table
Set rT = rT.Find(rM.Offset(0, 3))
If Not rT Is Nothing Then
' add one if it's found
rT.Offset(0, -1) = rT.Offset(0, -1).Value + 1
Else
' add a new date if it's not
rC.Value = 1
rC.Offset(0, 1).Value = rM.Offset(0, 3)
Set rC = rC.Offset(1, 0)
End If
Else
Set rT = wsNew.Range(oCol)
Set rT = rT.Find(rM.Offset(0, 2))
If Not rT Is Nothing Then
rT.Offset(0, -1) = rT.Offset(0, -1).Value + 1
Else
rO.Value = 1
rO.Offset(0, 1).Value = rM.Offset(0, 2)
Set rO = rO.Offset(1, 0)
End If
End If
Set rM = rM.Offset(1, 0) ' next record
Loop

' clean up
Set rT = Nothing
Set rC = Nothing
Set rO = Nothing
Set rM = Nothing
Set wsOld = Nothing
Set wsNew = Nothing
End Sub