I have a spreadsheet that has all the number of times I closed a ticket in any given day. Each row has a closed ticket.
The column headers are TICKET and DATE CLOSED
I want to sort that whole spreadsheet and see a Count, per day of the closed tickets. Basically trying to determine easily what day my greatest number of tickets were closed.
Thank you,
Jeff
Hi Jeff,
It would appear this could be done with a pivot table, but how about a sample sheet.
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
HTH
Regards, Jeff
My first thought is to use a pivot table ( https://www.excel-easy.com/data-****...ot-tables.html if you are unfamiliar with pivot tables). Set up a pivot table with date closed as your row labels and count of ticket in the values field.
Will that work for you?
Originally Posted by shg
I can't attach files per company policy but here is a csv partial dump for the data.
The resulting worksheet (with the Pivot table I guess) would show, by DATE CLOSED, the COUNT of the TICKETS
TICKET,Assignment Group,Assignee,Status,Title,DATE CLOSED
RM21217060,Directory Support,"Patron, Jeff",Closed,Internal Add DNS Request,5/15/2019 13:40
RM21217078,Directory Support,"Patron, Jeff",Closed,Internal Add DNS Request,5/15/2019 13:44
RM21216363,Directory Support,"Patron, Jeff",Closed,External Add DNS Request,5/16/2019 11:09
RM21217478,Directory Support,"Patron, Jeff",Closed,Internal Change DNS Request,5/17/2019 13:21
RM21203491,Directory Support,"Patron, Jeff",Closed,Internal Delete DNS Request,5/17/2019 14:31
RM21216563,Directory Services DNS,"Patron, Jeff",Closed,Unknown Device - remove DNS server,5/17/2019 15:02
RM21229404,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 10:43
RM21229421,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 12:28
RM21229384,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 12:38
RM21235220,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 14:01
RM21235325,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 14:12
RM21235411,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 14:33
RM21235948,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 14:42
RM21235954,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 14:48
RM21235965,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 15:10
RM21235989,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 15:14
RM21236009,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 15:20
RM21236015,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 15:26
RM21236016,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 15:50
RM21238063,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/22/2019 9:53
RM21239085,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/22/2019 10:00
RM21239114,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/22/2019 10:39
RM21239467,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/22/2019 11:29
RM21239500,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/22/2019 11:34
RM21240199,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/23/2019 11:12
RM21240213,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/23/2019 11:26
RM21244243,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/23/2019 11:58
RM21242231,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/23/2019 13:22
RM21242264,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/23/2019 13:50
RM21245069,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/23/2019 14:21
RM21245096,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/23/2019 14:42
RM21228681,Directory Services DNS,"Patron, Jeff",Closed,EZ Cap Upgrade - Change DNS Entries (5/17),5/24/2019 18:45
RM21253313,Directory Services DNS,"Patron, Jeff",Closed,External Change DNS Request,5/27/2019 10:53
RM21253316,Directory Services DNS,"Patron, Jeff",Closed,Internal Delete DNS Request,5/27/2019 12:03
RM21253325,Directory Services DNS,"Patron, Jeff",Closed,Internal Delete DNS Request,5/27/2019 12:20
RM21253587,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/27/2019 12:28
RM21253838,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/27/2019 12:33
RM21254311,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/27/2019 13:42
RM21253998,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/27/2019 13:42
RM21254339,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/27/2019 14:15
RM21255750,Directory Services DNS,"Patron, Jeff",Closed,Internal Delete DNS Request,5/27/2019 15:26
RM21257482,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/28/2019 9:50
RM21257503,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/28/2019 9:59
RM21257529,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/28/2019 10:09
RM21251440,Directory Services DNS,"Patron, Jeff",Closed,Internal Delete DNS Request,5/28/2019 12:02
RM21254049,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/28/2019 12:53
RM21262464,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 9:56
RM21260744,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/29/2019 10:28
RM21260525,Directory Services DNS,"Patron, Jeff",Closed,External Add DNS Request,5/29/2019 12:40
RM21262461,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/29/2019 13:50
RM21263231,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 14:04
RM21263242,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 14:13
RM21263315,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 14:24
RM21263310,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 14:26
RM21263438,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 15:09
RM21263460,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 15:14
RM21264262,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 17:14
RM21263429,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 10:01
RM21263723,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 12:27
RM21263729,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 12:34
RM21263732,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 12:40
RM21269157,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 14:37
RM21258924,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 16:18
RM21272025,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/31/2019 16:24
RM21298535,Directory Services DNS,"Patron, Jeff",Canceled,Internal Add DNS Request,6/3/2019 10:06
RM21298672,Directory Services DNS,"Patron, Jeff",Canceled,Internal Add DNS Request,6/3/2019 10:28
RM21298660,Directory Services DNS,"Patron, Jeff",Canceled,Internal Add DNS Request,6/3/2019 10:29
RM21272323,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,6/4/2019 9:54
RM21279115,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,6/4/2019 10:38
RM21271682,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,6/4/2019 11:08
RM21268187,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,6/4/2019 11:23
How about this pivot table.
Thanks Jeffrey, that's great and having it by day or month is even slicker. How do I learn to do your solve?
You are very welcome and thanks for the feedback. I would start with the reference link MrShorty provided, but here's another one.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Jeffrey, I don't see how you added a "Months" Field as well as "Count" Field, which wasn't present in my original spreadsheet. I followed your link on Pivot tables but the explanation is not there either.
Last edited by Zenmusicman; 07-23-2019 at 07:28 PM.
In this case, it actually did it for me, but you want to look into grouping.
https://www.contextures.com/xlPivot07.html
Still doesn't explain "Count". How do you add that field?
When you first make the pivot table you will add in the PivotTable Fields list...
Rows >> Date Closed
Value >> Status (The label should say Count of Status)
Just drag the fields from the top box down to the four quadrants. Only the bottom two quadrants will have data. Rows and values
Now in the Pivot Table itself, right click on a date and choose group. Highlight month and days then OK
After you have group by months and days, not the Rows box in the PivotTable Fields list with have Months and Date Closed.
Ok That got me in the ball park, thank you....however, I dragged as follows in the ROWS quadrant
Yours:
DATE CLOSED
Months
Mine:
Months
DATE CLOSED
I don't see how you got that handy Total count per month. (In yours, this happens regardless how I order the ROWS quadrant).
i.e.
May Total
Jun Total
Jeffrey the other thing your Pivot has that mine doesn't is 3 columns. Mine only has 2 (Months and Count of Status)
Maybe it's related to my earlier post but I like how you have columns as such:
DATE CLOSED Months Count of Status
The first two change as to how I order the ROWS quadrant but we still have 3 columns.
All my PivotTable Fields are the same as yours
For the month totals in the PT, highlight the PT, go to Design on the ribbon then to the far left under layout. Now adjust the Subtotals and Grand Totals. Turn them On and Off as you require.
As for the 3 columns versus 2, if you download the posted sample file you can see what has been done.
Thanks the subtotals (and grand totals) formatting worked for me. Very handy.
I have exactly the same Fields as you (your screen shots) but still only show 2 columns.
Would it perhaps be a PT formatting issue because Excel is displaying essentially one column,
twice (on yours) and you've achieved a different view of the data.
Your PT covers columns I(Months),J(DATE CLOSED),K(Count of Status). Mine, just I(Months) and J(Count of Status).
Could it be that you need to change the report layout to tabular?
Click on the PT and then on the ribbon
Design >> Layout >> Report Layout >> Show in Tabular Form
Voila! Thanks Jeff, I learned a lot, end-to-end.
Closed thread.
You are very welcome and thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks