Count or other suggestion.

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
 
P

Patrick Molloy

if the Tech Name is repeated in every row, then you only need to use an
AutoFilter selecting null in the HHHC column
 
O

OKY

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
 
P

Patrick Molloy

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
 
O

OKY

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...
 
P

Patrick Molloy

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count formula 2
REPORT OF JOBS PENDING 3
report of blank fields 1
Time management 1
Find and return data based on a range 7
What Am I Missing? Need A Form That, When Submitted, Updates/Modifies Existing Data in SharePoint Li 3
Use data to copy/fill other cells. 3

Top