average calls per hour

[MelanieB]

I need to show a graph of the average number of calls received per hour (worksheet sample attached).

So, from midnight-1am, 1am-2am, 2am-3am, etc.

From the data I have in the attached worksheet, how can I do that?
We don't care what day the call came in. Just the hour.

I'm visualizing a line graph?

[HansV]

I'd create a pivot table and pivot chart based on the data. You can group the times by hour in the pivot table.

See the attached version.

Average calls per hour.xlsx

[MelanieB]

perfect.. I was playing with a pivot table myself, but I couldn't figure out how to display the time as just the hour. How did you do that? I set my number format for the time field to the same as yours and it displays 5:49:00 PM but yours is just 5

[HansV]

Right-click any of the items in the Rows area of the pivot table.
Select Group... from the context menu.
Clear all selected (highlighted) options, then select Hours.
Click OK.

[MelanieB]

never mind.. you said group by time and I didn't do that.. when I did it that worked, but I can't get rid of the am/pm.... working on that figuring out how you did that now :)

[MelanieB]

grr ok so how did you get the am/pm not to display?

[HansV]

Did you group by Hours?

S0090.png

Do you still see AM/PM when you do that?

[MelanieB]

I did group by hours and it displays the am/pm

[MelanieB]

gack. I picked hours promise! just did the wrong screen snip

[HansV]

Could it be because I use the 24-hour clock in my system settings, and you use a 12-hour clock?

[MelanieB]

In case you want to see what I did in the actual file, I'm attaching it again after I tried to do the pivot.

[HansV]

According to Changing format for grouped hours, it does indeed depend on your system settings:

The format for the grouped hour in pivot tables comes from the long hour format in Regional Settings. (...) When I change the format in Regional Settings and then refresh the pivot, the hour changes from 13 and 1 p.m.

[MelanieB]

awesome. Thank you for your help. My manager said he is fine with the am / pm showing.

[MelanieB]

We are transferring our electronic records to another software program so my exported files need to match up with the format required of the new program.

I've attached a sample of the spreadsheet I need help with.
I have one worksheet that lists all dogs in our kennel reservation system and the expiration of each of their vaccines.
I need to update a different worksheet with that same data but as one record for the dog, not a separate one for each vaccine.

So, in the example,
Jack has 3 records - one for each vaccine
Simon has 2 records - one for each vaccine

I need to update just the expiration date for each of these dogs' vaccines on their one record in the new format. I typed the information in the first record to show what we need.

We have about 1150 records for 525 dogs. Ugh.

I thought about a vlookup but I don't know how to do it with an IF.

My thoughts were, in the Bordatella (example) cell, use a formula that says, look up Jack in the first table and if column E is Bordetella, then insert the expire date (col F); then do the same for the other vaccine columns.

This is a sample so the actual column numbers aren't the same in my real data. But, they are tables, with these same headings.

Can you help me please?

Thank you!

[HansV]

Do you already have the complete list of dogs (one per row) for the second spreadsheet, or should that be constructed automatically?

(An example of the actual layout, with irrelevant columns left empty, would be helpful)

[HansV]

Another thing: does one sheet really have "Bordetella" and the other "Bordatella"?

(And I assume that "Speices" is just a typo for "Species")

Here is a macro that will populate the second sheet from scratch, assuming that the first row (the headings) has been filled.

Code: Select all

Sub CopyData()
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim s As Long
    Dim m As Long
    Dim t As Long
    Dim c As Long
    Dim n As Long
    Dim d As Long
    Dim colS As New Collection
    Dim colT As New Collection
    Application.ScreenUpdating = False
    Set wshS = Worksheets("Expiry")
    n = wshS.Cells(1, wshS.Columns.Count).End(xlToLeft).Column
    For c = 1 To n
        colS.Add Item:=c, Key:=wshS.Cells(1, c).Value
    Next c
    Set wshT = Worksheets("New")
    n = wshT.Cells(1, wshT.Columns.Count).End(xlToLeft).Column
    For c = 1 To n
        colT.Add Item:=c, Key:=wshT.Cells(1, c).Value
    Next c
    t = 1
    m = wshS.Cells(wshS.Rows.Count, 1).End(xlUp).Row
    For s = 2 To m
        If wshS.Cells(s, colS("Customer")).Value <> wshS.Cells(s - 1, colS("Customer")).Value Or wshS.Cells(s, colS("Pet Name")).Value <> wshS.Cells(s - 1, colS("Pet Name")).Value Then
            t = t + 1
            wshT.Cells(t, colT("Customer ID")).Value = wshS.Cells(s, colS("ID")).Value
            wshT.Cells(t, colT("Pet Name")).Value = wshS.Cells(s, colS("Pet Name")).Value
            wshT.Cells(t, colT("Column1")).Value = wshT.Cells(t, colT("Customer ID")).Value & " " & wshS.Cells(s, colS("Pet Name")).Value
            wshT.Cells(t, colT("Species")).Value = wshS.Cells(s, colS("Pet Type")).Value
        End If
        Select Case True
            Case wshS.Cells(s, colS("Expiring Vaccine")).Value Like "Bordetella*"
                c = colT("Bordatella Expiration Date (dog)")
            Case wshS.Cells(s, colS("Expiring Vaccine")).Value Like "Rabies*"
                c = colT("Rabies Expiration Date (dog)")
            Case wshS.Cells(s, colS("Expiring Vaccine")).Value Like "DHLLP*"
                c = colT("DHPP Expiration Date (dog)")
        End Select
        wshT.Cells(t, c).Value = wshS.Cells(s, colS("Expire Date")).Value
    Next s
    Application.ScreenUpdating = True
End Sub

See the attached version. It should work if the columns are in other locations too.

Vaccines - help.xlsm

[MelanieB]

Yes, I have a worksheet with one row per dog. There are 542 records.
The macro worked perfectly! Thank you!

I struggle with creating/writing macros, so I don't usually even attempt that. I was trying to force a vlookup of some kind.

Thank you so very very much!