Solved

Pulling information in Excel using a date range

Posted on 2014-03-12
18
272 Views
Last Modified: 2014-03-29
I have a spreadsheet, Weekly TTIR, in which I would like to utilize a date range to pull the information for my master spreadsheet, IM Raw Data.  My intent is utilize the Date Picker in cells J4 and K4 to pick the date range of information I need to report on.  This date picker was created by Goflow for my in a previous question.  I though about using the indexing option to fill this information but am struggling with getting it pull back certain cells of information, Open Date, Ticket Number, IR and Impact, instead of the entire row which contains much more information.

I have added file in which I am currently working.  Hope, that is clear enough.
Copy-of-Prouduction-IM-METRICS-T.xlsm
0
Comment
Question by:Rrave26
  • 9
  • 9
18 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Same story as before ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Before you had a formula now you have values !!!

Where do you need the data to be put ?

I though about using the indexing option to fill this information but am struggling with getting it pull back certain cells of information,
?? Not clear

gowflow
0
 

Author Comment

by:Rrave26
Comment Utility
I am currently manually cutting and pasting these values to this report.  I would like to automate this data to be updated within the formating I had created cells populate cells A4 : H*.  

Regarding the Indexing option.  I was doing some research on formulas to use and the indexing formula looked like it was a good option.  However, I since I am only pulling certain fields from a record,  column b, column C, column  J, column G, column Aj, column AL, I was unsure how to accomplish this.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok but from where ???

You need to tell me from where the data needs to endup in this sheet Weekly TTIR as in here you only have values.

gowflow
0
 

Author Comment

by:Rrave26
Comment Utility
Ok, I think this is what you are looking for

Date
From                                         TO
IR RAW DATA Column J            Weekly TTIR Column B

Ticket Number
From                                         To
IR RAW DATA Column B           Weekly TTIR Column c

IR
From                                         To
IR RAW DATA Column C            Weekly TTIR Column d

Crisis Call Start Time
From                                         To
IR RAW DATA Column AJ            Weekly TTIR Column e

Time Initial IR sent
From                                         To
IR RAW DATA Column AL            Weekly TTIR Column f

Impact
From                                         To
IR RAW DATA Column G            Weekly TTIR Column H

I can calculate column G on the Weekly TTIR report from Column E and F on the dame page.  

My thought was to use the date range VB solution you created on the last report to pick the date range that I want to display.  Hope this helps.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
You need this as a formula or we can do VBA solution ?

If you want my input, then I am more incline to do VBA but if you want a formula, then I will have to work on it !!

gowflow
0
 

Author Comment

by:Rrave26
Comment Utility
If you are giving me an option, then I would love to see a VBA solution.  I would eventually love to have all of my reports be a VBA solution, but I simply don't have the skill set yet.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well you got the person for that !!!! :)

Give me sometime and you will get something that hopefully you will like.

I have a question though
1) Is ee at the end of the report some data like
Notes:      T19667202      Buffalo lack of particiaption.                              
...

and Percent Participation ...

I need the details for this are they manually written or pulled from some sheet ?
How do you calculate the Percent Participation ?

I need to have everything finetuned to the last detail so we can get this sheet correctly populated.

gowflow
0
 

Author Comment

by:Rrave26
Comment Utility
You don't need to worry about the notes section.  I have taken this outta of the report.  It's a non issue/requirement.

Thanks
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Rrave26
Comment Utility
As for the Percentage cell.  This was a manually calculated field that just showed the percentage of IR's that were compled in 15 mins or less.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok so to sum it up.

We have the data in the rows and at the end Percentage.

what is the formula ?
gowflow
0
 

Author Comment

by:Rrave26
Comment Utility
the formula would be:

# of IR's that met the goal of <=15 mins/ total number of IRs for that period.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok give me sometime I am working on it !!

Just one question, your data in sheet IR RAW DATA is always sorted by date ???
gowflow
0
 

Author Comment

by:Rrave26
Comment Utility
Yes it is and believe me I am not rushing you.  Lol.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
ok here it is:

I have created a button so you can generate the report after selecting the dates. Try selecting wrong dates or date that does not have data or date from bigger than date to and see the results.

I also put the formulas for totals.
Let me know your thoughts.

Here is the code it is in a module.

Sub WeeklyTTIR(StDate As Date, EndDate As Date)
Dim WS As Worksheet
Dim WSRaw As Worksheet
Const ColOddRow = 12040422
Dim MaxRow As Long, MaxCol As Long, I As Long, J As Long, LCount As Long, FirstRow As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> initialise Variables
Set WS = ActiveSheet
Set WSRaw = Sheets("IM Raw Data")
MaxRow = WSRaw.Range("A" & WSRaw.Rows.Count).End(xlUp).Row
MaxCol = WSRaw.Columns(WSRaw.Columns.Count).End(xlToLeft).Column
J = 4

'---> Clean Present Report
WS.Range("4:" & WS.Rows.Count).EntireRow.Delete

'---> Filter Data in IM raw Data for the selected period
If WSRaw.FilterMode = True Then WSRaw.ShowAllData
WSRaw.UsedRange.AutoFilter Field:=WSRaw.Columns("J").Column, Criteria1:=">=" & StDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

'---> Start Process
For I = 2 To MaxRow
    If WSRaw.Range("A" & I).EntireRow.Hidden = False Then
        If FirstRow = 0 Then FirstRow = J
        '---> Affect Data to Cells
        WS.Range("A" & J) = J - 3
        WS.Range("B" & J) = WSRaw.Cells(I, "J")
        WS.Range("C" & J) = WSRaw.Cells(I, "B")
        WS.Range("D" & J) = WSRaw.Cells(I, "C")
        If WSRaw.Cells(I, "AJ") <> "" Then WS.Range("E" & J) = TimeValue(WSRaw.Cells(I, "AJ"))
        If WSRaw.Cells(I, "AL") <> "" Then WS.Range("F" & J) = TimeValue(WSRaw.Cells(I, "AL"))
        WS.Range("G" & J).Formula = "=F" & J & "-E" & J
        WS.Range("H" & J) = WSRaw.Cells(I, "G")
    
        '---> Format the Row
        If J Mod 2 <> 0 Then WS.Range("A" & J & ":H" & J).Interior.Color = ColOddRow
        WS.Range("A" & J & ":H" & J).HorizontalAlignment = xlCenter
        WS.Range("A" & J & ":H" & J).Cells.BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        WS.Range("A" & J & ":H" & J).Cells.Borders.LineStyle = xlContinuous
        WS.Range("B" & J).NumberFormat = "dd-mmm-yyyy"
        WS.Range("E" & J & ":F" & J).NumberFormat = "hh:mm"
        WS.Range("G" & J).NumberFormat = "hh:mm:ss"
        
        '---> Increment Counters
        J = J + 1
        LCount = LCount + 1
        
    End If

Next I

If FirstRow <> 0 Then
    
    '---> Make final Formating and Display Totals
    WS.Range("A" & FirstRow & ":H" & J - 1).Cells.BorderAround LineStyle:=xlContinuous, Weight:=xlThick
    WS.Range("F" & J + 1) = "Average TTIR"
    WS.Range("F" & J + 2) = "Percent sent on time"
    
    '---> formulas
    '=TEXT(AVERAGE(G4:G14),"h:mm:ss")
    WS.Range("G" & J + 1).Formula = "=AVERAGE(G" & FirstRow & ":G" & J - 1 & ")"
    WS.Range("G" & J + 1).NumberFormat = "hh:mm:ss"
    
    '=TEXT(COUNTIF(G4:G14,"<=0:15:00")/COUNT(G4:G14),"0.0%")
    WS.Range("G" & J + 2).Formula = "=COUNTIF(G" & FirstRow & ":G" & J - 1 & "," & Chr(34) & "<=0:15:00" & Chr(34) & ")/COUNT(G" & FirstRow & ":G" & J - 1 & ")"
    WS.Range("G" & J + 2).NumberFormat = "0.0%"
    
    '---> Format Totals
    WS.Range("F" & J + 1 & ":G" & J + 2).Font.Bold = True
    WS.Range("F" & J + 1 & ":F" & J + 2).HorizontalAlignment = xlLeft
    WS.Range("G" & J + 1 & ":G" & J + 2).HorizontalAlignment = xlCenter
    
    '---> Change Title
    WS.Shapes("TextBox4").OLEFormat.Object.Text = "TTIR Weekly Report Ending " & Format(EndDate, "dd-mmm-yyyy") & " for Incident Management "
End If

'---> Disable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

'---> Advise User
If FirstRow <> 0 Then
    MsgBox ("Weekly Report for period ending " & EndDate & " gerated " & LCount & " records successfully.")
Else
    MsgBox ("No Data was found in this interval.")
End If

'---> Unfilter Data
If WSRaw.FilterMode = True Then WSRaw.ShowAllData


End Sub

Open in new window



gowflow
Prouduction-IM-METRICS-T-V01.xlsm
0
 

Author Closing Comment

by:Rrave26
Comment Utility
Again first rate in helping getting what I was looking for.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Glad I could help. Pls do not hesitate to put a link in here for any other question you may need help with.

gowlfow
0
 

Author Comment

by:Rrave26
Comment Utility
Ok, I just want to be clear that if I have another question that I can post it here?  I don't want to get anyone in trouble.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now