Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to create a pivot table that sums visits per day by provider: Date...Visits....Provider So, I can ask...how many visits did provider X have on day Y?
Solved by F. J. in 34 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 20/09/2018 - 05:26
Hello, I understand that you need help in creating a pivot table that will show you the number of times the of visit per day, right?
User 20/09/2018 - 05:28
Yes, please. When I go to create it I have to split the date.
User 20/09/2018 - 05:28
I need to keep the date whole.
Excelchat Expert 20/09/2018 - 05:28
Okay, May I know the relevance of the Dx 1 Dx2 etc?
User 20/09/2018 - 05:28
The Dx at the top are the different codes I'm working with.
Excelchat Expert 20/09/2018 - 05:29
So they are not necessary in the Pivot Table?
User 20/09/2018 - 05:29
I receive raw data like I've created for you.
User 20/09/2018 - 05:29
Then I have to sort it by date.
User 20/09/2018 - 05:29
They I need to create a pivot table.
Excelchat Expert 20/09/2018 - 05:30
In the Example you provided, the Pivot Table will result to 1 visit per day, right?
User 20/09/2018 - 05:30
Except when I go to create it the pivot table makes me split the date into three columns.
Excelchat Expert 20/09/2018 - 05:30
Okay, I'll help you with this one. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert 20/09/2018 - 05:30
I may have a few quick question to help me fully understand your requirement.
User 20/09/2018 - 05:30
I need to be able to know on June 7, 2018 how many appointments (time) provide D had.
Excelchat Expert 20/09/2018 - 05:31
Yes and in the example you've provided, Provider D only had one appointment on June 7, 2018, right?
User 20/09/2018 - 05:31
This is a free trial for me and I believe I have one question. Do I not?
Excelchat Expert 20/09/2018 - 05:32
Don't worry about that, it's a standard reminder we give.
User 20/09/2018 - 05:32
Yes, I receive a thousand rows of data so a manual sort isn't a good idea.
User 20/09/2018 - 05:32
Would you like me to add more data?
Excelchat Expert 20/09/2018 - 05:32
And yes, your question is about creating the Pivot Table to count the number of visit per provider so I'll help you with this one.
Excelchat Expert 20/09/2018 - 05:33
Yes please, adding more data would really help show you the result of what I'll work on.
Excelchat Expert 20/09/2018 - 05:33
Thank you. What do you want to see in the Pivot Table, just the date and the Provider and the number of visits?
User 20/09/2018 - 05:36
i would like to pull by date how many visits each provider has done. For example: Provider A did 10 visit on June 7, 2018 while Provider B did 7. Make sense?
Excelchat Expert 20/09/2018 - 05:37
Yes. I'll be working on this offline and prepare the steps. Please give me a few minutes.
Excelchat Expert 20/09/2018 - 05:37
Can you complete the sample data please?
Excelchat Expert 20/09/2018 - 05:37
Providers are incomplete starting row 36
User 20/09/2018 - 05:37
Working on it!
Excelchat Expert 20/09/2018 - 05:38
Oh, data on top is also missing a lot. Looks like we'll need more time since the sample data isn't completed yet. Please extend the session when you get a prompt.
Excelchat Expert 20/09/2018 - 05:41
That should be enough, the DX columns shouldn't matter at all so we can add random stuff in there.
User 20/09/2018 - 05:41
Ok....sorry...I didn't know I was going to need to create a sample or I would have done it prior.
Excelchat Expert 20/09/2018 - 05:45
I'm working on this locally on my Excel. Please give me a few minutes.
User 20/09/2018 - 05:45
Ok. Thank you.
Excelchat Expert 20/09/2018 - 05:48
Please download this file.
[Uploaded an Excel file]
User 20/09/2018 - 05:48
Downloading...
Excelchat Expert 20/09/2018 - 05:48
In this file I created 2 Pivot Table that both shows what you want to see but they are formatted a bit differently.
Excelchat Expert 20/09/2018 - 05:49
Let me know which one you prefer so I can tell you how it's done.
User 20/09/2018 - 05:50
This is awesome! I will so use you again. Let me think...I think the one with the grand totals please.
Excelchat Expert 20/09/2018 - 05:50
The left one or the right one?
Excelchat Expert 20/09/2018 - 05:50
Both have Grand Totals. :)
User 20/09/2018 - 05:51
The one on the right that has date...a,b,c,d...and grand total.
Excelchat Expert 20/09/2018 - 05:51
Okay, let me prepare the steps.
User 20/09/2018 - 05:51
The one on the right has grand total across and down. That one please.
Excelchat Expert 20/09/2018 - 05:51
You are familiar with creating a Pivot Table, right? It's just the right settings that you need help with.
User 20/09/2018 - 05:51
Yes, I know how to insert a pivot table.
Excelchat Expert 20/09/2018 - 05:51
Okay, I'll skip that part.
User 20/09/2018 - 05:53
After you help me, and I try it on my own, can I come back (understanding I need to pay) and get help with you again?
Excelchat Expert 20/09/2018 - 05:54
Of course but I can't guarantee that you will be able to work with me since you may be assigned with another expert.
Excelchat Expert 20/09/2018 - 05:54
All of us are equally able to help you though so don't worry. Just make sure the description is accurate so the right expert will be assigned to you.
User 20/09/2018 - 05:55
And do I reference the problem number: 45426?
Excelchat Expert 20/09/2018 - 05:55
Anyway, please go to the INSTRUCTIONS tab in the sheet to the right.
Excelchat Expert 20/09/2018 - 05:55
No, just type a description again.
Excelchat Expert 20/09/2018 - 05:56
Are you in the INSTRUCTIONS Tab?
User 20/09/2018 - 05:56
Yes, I know this.
User 20/09/2018 - 05:56
I have a 2 pm call.
Excelchat Expert 20/09/2018 - 05:56
Okay, that's all you really need to do to recreate the Pivot Table like the one in the file I sent you.
User 20/09/2018 - 05:57
The date...it doesn't like the date when I do the instructions you provided.
Excelchat Expert 20/09/2018 - 05:57
Alright, save the instructions and try it on your file later.
Excelchat Expert 20/09/2018 - 05:57
Did you UNGROUP?
User 20/09/2018 - 05:57
No, I didn't do ungroup. I think you're right....that must be it. Let me try it and if it doesn't work I'll come back. You have been most kind. Thank you.
Excelchat Expert 20/09/2018 - 05:58
When dragging the Date under ROWS, Excel may group the date so it will show Year QTR and Month instead.
Excelchat Expert 20/09/2018 - 05:58
By Ungrouping you will be able to view individual dates.
User 20/09/2018 - 05:58
Ah, that must be the problem then. You are awesome!
Excelchat Expert 20/09/2018 - 05:58
Alright, I know you will have to attend a meeting soon so would there be anything else that I can help you with regards to the original question?
User 20/09/2018 - 05:59
No, thank you. I will try it. Have a lovely day..
Excelchat Expert 20/09/2018 - 05:59
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert 20/09/2018 - 05:59
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert 20/09/2018 - 05:59
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc