Count or other suggestion.

  • Thread starter Thread starter OscarE
  • Start date Start date
O

OscarE

Hi
I am makeing a worksheet for the dispatchers ast works.
This is what I did so far.
TECH NAME JOB# TIME HHHC STATUS
LUIS G 1R 8 - 11am fail Completed
3R 11 - 2pm
5R 2 - 5PM
Tech name, time, HHHC and status have a drop down list. their reference is
in cell AA.

So what I need the dispatch to manualy type is the Job#, anything else is a
drop down list or combo.
I need to have in sheet2 a report of all the jobs that still not completed
according to their time scheduled.. For example:

TECH NAME TIMEFRAME QTY TOTAL JOBS OPEN
LUIS G 11 - 2PM 1 2
2 - 5PM 1


My boss wants to know how many jobs still not completed by timeframe and the
technician responsible for that open job. It will be a report that I have to
email several times a day.

I need your help ASAP if possible.
Thank you
 
if the Tech Name is repeated in every row, then you only need to use an
AutoFilter selecting null in the HHHC column
 
The Technician name will change. There are 22 technicians , and each will get
a route of 10 jobs daily. I need to send a report to my boss of who is not
closing their jobs ontime. For example if a technician has 4 jobs from 8 -
11am and is already 10 am , let's say he has completed 2 jobs . He is not
gonna make it ontime and my boss wants to know that.
thanks
 
so your table of jobs is Sheet1 and the output will go to sheet2

copy this into a standard code module

Option Explicit

Sub GetReport()
Dim rowindex As Long
Dim worker As String
Dim rw As Long

'clear target & add headers
With Worksheets("Sheet2")
.Cells.Clear
.Range("A1") = Range("A1")
.Range("B1") = Range("C1")
End With
rowindex = 1

'check jobs
For rw = 2 To Range("B1").End(xlDown).Row
If Cells(rw, 1) <> "" Then
worker = Cells(rw, 1)
End If
'check status
If Cells(rw, "E") <> "Completed" Then
rowindex = rowindex + 1
With Worksheets("Sheet2")
.Cells(rowindex, 1) = worker
.Cells(rowindex, 2) = Cells(rw, 3)
End With
End If
Next

Summarize

End Sub

Sub Summarize()
With Worksheets("sheet2")
.Range("E1") = .Range("A1")
.Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("E1"), _
Unique:=True
If .Range("e3") <> "" Then
.Range(.Range("E2"), .Range("E2").End(xlDown)).Offset(,
1).FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
Else
.Range("F2").FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
End If
End With
End Sub

it checks the Status column, if the word Completed isn't there, then the
workers name and job time gets copied to the report ( this is sheet2)
when all the names are read, the summary is added.

To add a code module, go to the development environment, ALT+F11, then from
the menu choose INSERT then MODULE

this should be enough to get you going. Let me know what else we can help
with
 
for some reason is not working. I get a Sintax error.
To give you an idea how my sheet looks, I have a link:
http://cid-515b3cfce2772b8a.skydrive.live.com/self.aspx/.Public/EXCEL FILES/TEST.xls
Sheet2 is how the report may look.
Thank you for your help.

Patrick Molloy said:
so your table of jobs is Sheet1 and the output will go to sheet2

copy this into a standard code module

Option Explicit

Sub GetReport()
Dim rowindex As Long
Dim worker As String
Dim rw As Long

'clear target & add headers
With Worksheets("Sheet2")
.Cells.Clear
.Range("A1") = Range("A1")
.Range("B1") = Range("C1")
End With
rowindex = 1

'check jobs
For rw = 2 To Range("B1").End(xlDown).Row
If Cells(rw, 1) <> "" Then
worker = Cells(rw, 1)
End If
'check status
If Cells(rw, "E") <> "Completed" Then
rowindex = rowindex + 1
With Worksheets("Sheet2")
.Cells(rowindex, 1) = worker
.Cells(rowindex, 2) = Cells(rw, 3)
End With
End If
Next

Summarize

End Sub

Sub Summarize()
With Worksheets("sheet2")
.Range("E1") = .Range("A1")
.Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("E1"), _
Unique:=True
If .Range("e3") <> "" Then
.Range(.Range("E2"), .Range("E2").End(xlDown)).Offset(,
1).FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
Else
.Range("F2").FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
End If
End With
End Sub

it checks the Status column, if the word Completed isn't there, then the
workers name and job time gets copied to the report ( this is sheet2)
when all the names are read, the summary is added.

To add a code module, go to the development environment, ALT+F11, then from
the menu choose INSERT then MODULE

this should be enough to get you going. Let me know what else we can help
with





OscarE said:
Hi
I am makeing a worksheet for the dispatchers ast works.
This is what I did so far.
TECH NAME JOB# TIME HHHC STATUS
LUIS G 1R 8 - 11am fail Completed
3R 11 - 2pm
5R 2 - 5PM
Tech name, time, HHHC and status have a drop down list. their reference is
in cell AA.

So what I need the dispatch to manualy type is the Job#, anything else is
a
drop down list or combo.
I need to have in sheet2 a report of all the jobs that still not completed
according to their time scheduled.. For example:

TECH NAME TIMEFRAME QTY TOTAL JOBS OPEN
LUIS G 11 - 2PM 1 2
2 - 5PM 1


My boss wants to know how many jobs still not completed by timeframe and
the
technician responsible for that open job. It will be a report that I have
to
email several times a day.

I need your help ASAP if possible.
Thank you
Click to expand...
Click to expand...
 
file is here:
http://cid-b8e56c9a5f311cb7.skydrive.live.com/browse.aspx/.Public/Excel Files/Martini

run procedure "Main"

full code (in the file)

Option Explicit
Dim rowindex As Long
Sub Main()
GetReport Range("A3:F13"), True
GetReport Range("A18:F29")
GetReport Range("H3:M14")
GetReport Range("H18:M29")
Summarize
End Sub
Sub GetReport(table As Range, Optional first As Boolean = False)
Dim worker As String
Dim rw As Long
Dim cell As Range
'clear target & add headers
If first Then
'initialise
With Worksheets("Sheet2")
.Cells.Clear
.Range("A2:C2") = Array("TECH NAME", "JOBS OPEN", "TIMEFRAME")
End With
rowindex = 2
End If
'check jobs
worker = table.Range("A1")
For Each cell In table.Columns(2).Cells
If cell.Value = "" Then Exit For
If cell.Offset(, 3).Value <> "CP" Then
rowindex = rowindex + 1
With Worksheets("Sheet2")
.Cells(rowindex, 1) = worker
.Cells(rowindex, 2) = cell.Value
.Cells(rowindex, 3) = cell.Offset(, 1)
End With
End If
Next
End Sub
Sub Summarize()
With Worksheets("sheet2")
.Range("E2") = .Range("A2")
.Range("A2").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("E2"), _
Unique:=True
If .Range("e4") <> "" Then
.Range(.Range("E3"), .Range("E3").End(xlDown)).Offset(,
1).FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
Else
.Range("F3").FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
End If
End With
End Sub






OKY said:
for some reason is not working. I get a Sintax error.
To give you an idea how my sheet looks, I have a link:
http://cid-515b3cfce2772b8a.skydrive.live.com/self.aspx/.Public/EXCEL FILES/TEST.xls
Sheet2 is how the report may look.
Thank you for your help.

Patrick Molloy said:
so your table of jobs is Sheet1 and the output will go to sheet2

copy this into a standard code module

Option Explicit

Sub GetReport()
Dim rowindex As Long
Dim worker As String
Dim rw As Long

'clear target & add headers
With Worksheets("Sheet2")
.Cells.Clear
.Range("A1") = Range("A1")
.Range("B1") = Range("C1")
End With
rowindex = 1

'check jobs
For rw = 2 To Range("B1").End(xlDown).Row
If Cells(rw, 1) <> "" Then
worker = Cells(rw, 1)
End If
'check status
If Cells(rw, "E") <> "Completed" Then
rowindex = rowindex + 1
With Worksheets("Sheet2")
.Cells(rowindex, 1) = worker
.Cells(rowindex, 2) = Cells(rw, 3)
End With
End If
Next

Summarize

End Sub

Sub Summarize()
With Worksheets("sheet2")
.Range("E1") = .Range("A1")
.Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("E1"), _
Unique:=True
If .Range("e3") <> "" Then
.Range(.Range("E2"), .Range("E2").End(xlDown)).Offset(,
1).FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
Else
.Range("F2").FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
End If
End With
End Sub

it checks the Status column, if the word Completed isn't there, then the
workers name and job time gets copied to the report ( this is sheet2)
when all the names are read, the summary is added.

To add a code module, go to the development environment, ALT+F11, then
from
the menu choose INSERT then MODULE

this should be enough to get you going. Let me know what else we can help
with





OscarE said:
Hi
I am makeing a worksheet for the dispatchers ast works.
This is what I did so far.
TECH NAME JOB# TIME HHHC STATUS
LUIS G 1R 8 - 11am fail Completed
3R 11 - 2pm
5R 2 - 5PM
Tech name, time, HHHC and status have a drop down list. their reference
is
in cell AA.

So what I need the dispatch to manualy type is the Job#, anything else
is
a
drop down list or combo.
I need to have in sheet2 a report of all the jobs that still not
completed
according to their time scheduled.. For example:

TECH NAME TIMEFRAME QTY TOTAL JOBS OPEN
LUIS G 11 - 2PM 1 2
2 - 5PM 1


My boss wants to know how many jobs still not completed by timeframe
and
the
technician responsible for that open job. It will be a report that I
have
to
email several times a day.

I need your help ASAP if possible.
Thank you
Click to expand...
Click to expand...
Click to expand...
 
Back
Top