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: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
wrote:
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
Dave F wrote:
=COUNTIF(B2:B5="New") etc.
Dave
--
Brevity is the soul of wit.
" wrote:
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!
--