[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Pulling information in Excel using a date range

Posted on 2014-03-12
18
Medium Priority
?
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
18 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39924220
Same story as before ?
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39924229
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
ID: 39924325
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 31

Expert Comment

by:gowflow
ID: 39924384
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
ID: 39924417
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 31

Expert Comment

by:gowflow
ID: 39924432
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
ID: 39924488
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 31

Expert Comment

by:gowflow
ID: 39924613
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
ID: 39924647
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
 

Author Comment

by:Rrave26
ID: 39924686
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 31

Expert Comment

by:gowflow
ID: 39924801
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
ID: 39926088
the formula would be:

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

Expert Comment

by:gowflow
ID: 39926152
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
ID: 39926550
Yes it is and believe me I am not rushing you.  Lol.
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 39926572
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
ID: 39927150
Again first rate in helping getting what I was looking for.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39927396
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
ID: 39964097
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

650 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